php - 使用 mssql php 在 SQL 数据库中插入文件内容时出错

Question

我正在尝试使用 PHP 和 MSSQL 将文件(pdf、图像)上传到数据库中。

<form role="form" action="kyc.php" method="post" enctype="multipart/form-data">
    <div class="form-body">

        <div class="row">
            <div class="col-md-offset-1">    
                <div class="form-group">
                    <label for="aadhaar">Aadhaar Card</label>
                    <input type="file" name="aadhaar" id="aadhaar" accept="application/pdf,image/x-png,image/gif,image/jpeg" required>
                </div>
            </div>
        </div>

        <input type="hidden" value="<?php echo $client_id; ?>" name="clientCode">
    </div>
    <div class="form-actions">
        <button name="submit" type="submit" class="btn blue">Upload</button>
    </div>
</form>

这是在表单提交后执行的 PHP 代码:

$clientCode = $_POST['clientCode'];
$fileName=$_FILES["aadhaar"]["name"];
$fileType = $_FILES['aadhaar']['type'];

//Get the content of the image and then add slashes to it 
$fileTemp=addslashes(file_get_contents($_FILES['aadhaar']['tmp_name']));

//Insert the image name and image content in image_table
$query="INSERT INTO KYCSCANS (FIRMNUMBER, CLIENTCODE, FILENAME, FILETYPE, FILECONTENT, TIMESTAMP, CREATIONTIMESTAMP, ADDEDBY, FILERELATED) VALUES('Global','{$clientCode}', '{$fileName}', '{$fileType}', '{$fileTemp}', GETDATE(), GETDATE(), 'madWeb', 'temp')";

$insertQuery = sqlsrv_query($jarvisconnection, $query);

if(!$insertQuery) {
    die("Aadhaar Query Failed" . print_r(sqlsrv_errors(), true));
}

这是表结构-

SRNO - int
FIRMNUMBER - nchar(10)
CLIENTCODE - nvarchar(50)
FILENAME- nvarchar(50)
FILETYPE - nvarchar(50)
FILECONTENT - varbinary(MAX)
TIMESTAMP - datetime
CREATIONTIMESTAMP - datetime
ADDEDBY - nchar(15)
FILERELATED - nvarchar(50)

我尝试了很多方法，但仍然出现以下错误:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]Syntax error, permission violation, or other nonspecific error [message] => [Microsoft][ODBC Driver 11 for SQL Server]Syntax error, permission violation, or other nonspecific error ) )

Answer 1

它可能是任何数量的东西，但最有可能的罪魁祸首是文件中的二进制内容。使用适当的参数化以确保数据经过清理并提高安全性。您还应该对数据进行一些检查，以确保数据对于您的数据库列来说不会太长，并根据需要截断或拒绝。

<?php
$clientCode = $_POST["clientCode"];
$fileName   = $_FILES["aadhaar"]["name"];
$fileType   = $_FILES["aadhaar"]["type"];
$fileTemp   = file_get_contents($_FILES["aadhaar"]["tmp_name"]);
$parameters = array(
    $clientCode,
    $fileName,
    $fileType,
    array($fileTemp, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING, SQLSRV_SQLTYPE_BINARY)
);
$query      = <<<SQL
INSERT INTO KYCSCANS (
    FIRMNUMBER, CLIENTCODE, FILENAME, FILETYPE, FILECONTENT, 
    TIMESTAMP, CREATIONTIMESTAMP, ADDEDBY, FILERELATED
) 
VALUES (
    'Global', ?, ?, ?, ?, GETDATE(), GETDATE(), 'madWeb', 'temp'
)
SQL;

$insertQuery = sqlsrv_query($jarvisconnection, $query, $parameters);

if ($insertQuery === false) {
    die("Aadhaar Query Failed" . print_r(sqlsrv_errors(), true));
}