SQL Server替换php变量中的撇号

Question

I'm trying to remove apostrophes within certain fields of a SQL Server query within PHP, and despite the fact that I'm throwing in the extra set of ''s in the string pattern section, it still errors out when an apostrophe is found within the result.. I'm not sure what I'm doing wrong here, any help would be hugely appreciated!

This section of the code loops through each row result from a web scrape and throws it into a new table row for each result. The functionality works, but it errors out and breaks the loop whenever an apostrophe is returned by the query.

for($i = 1, $max = count($table); $i < $max; ++$i) {
$dbrow = $table[$i];
$insquery = "INSERT INTO db.schema.table (EVENT_ID, EVENT_CODE, DESCRIPTION, 
STATUS, SITE_STATUS, CITY, COUNTY, ZIP, STATE, STARTDATE, ENDDATE, YEARS_PRIOR, TOTAL_PRICE,
CUSTODIAN_FEE, ESTIMATED_FEES, FEE_TYPE, SD, PROGRAM, RC_LN, BUF_AMOUNT, LOC_PREM, 
ST_PREM, SNAPSHOT_DATE) VALUES('".$dbrow[0]."','".$dbrow[1]."', replace('".$dbrow[2]."','''', ''),'".$dbrow[3]."','".$dbrow[4]."', replace('".$dbrow[5]."','''', ''), replace('".$dbrow[6]."','''', ''),'".$dbrow[7]."','".$dbrow[8]."','".$dbrow[9]."','".$dbrow[10]."','".$dbrow[11]."','".$dbrow[12]."','".$dbrow[13]."','".$dbrow[14]."','".$dbrow[15]."', replace('".$dbrow[16]."','''', ''), replace('".$dbrow[17]."','''', ''), replace('".$dbrow[18]."','''', ''), replace('".$dbrow[19]."','''', ''),'".$dbrow[20]."','".$dbrow[21]."','".$dbrow[22]."');";
sqlsrv_query($conn, $insquery) or die ("Error in query: $insquery. ".sqlsrv_errors());
echo $insquery . "<br>";
};

Answer 1

Of course it does. You're trying to replace the apostrophes in SQL itself. Consider this statement:

replace('don't','''', '')

How is SQL Server supposed to evaluate that? It opens a string, then after three letters closes that string, then encounters an errant t character resulting in a syntax error.

The query itself needs to be syntactically valid for SQL to execute it. SQL can't fix syntax errors in its own code. You would need to conduct the replacement for single-quotes in PHP, not in SQL.

In any event, there's a really neat solution to all of this. Prepared statements. Don't worry about quoting at all, let the engine handle that for you. For example:

$sql = "INSERT INTO Table_1 (id, data) VALUES (?, ?)";
$params = array(1, "some data");

$stmt = sqlsrv_query($conn, $sql, $params);

This, of course, has the added benefit of helping to protect you from SQL injection attacks, something to which your current code is very likely vulnerable.