Best way to handle double quotes? "How?"

[benphp]

I'm using double apostrophes for apostrophes, but double quotes are still a problem with PHP/MS SQL.

 

Is there any way to get actual double quotes in the db and not faking it with two single quotes?

 

"This" not ''This''

 

 

[trq]

If your not using PDO with MSSQL you should be.

[Doug G]

Microsoft DB's escapes double quotes by doubling them up.  Similar to \".  Just use two double quotes in your sql text and just one double quote ends up in the db.

[benphp]

I think I may have done it. I'm used to massaging strings to input into mysql, so I was probably overdoing it.

 

When updating the DB, I first run the string through fnTick:

 

function fnTick($string) {
$string = str_replace("'", "''", $string); 
return $string;
}

 

This takes care of the single quotes (or apostrophes) for my SQL statement. The db removes the second single quote for me - thanks, db.

 

UPDATE mytable 
SET 
desc = 'My test string here is "tester" test "quote" test''s',
name = 'This is "test''s"  ''test'' working "tester" test "quote" test''s Here''s another line "quote" '
WHERE 
ID = 737

 

Note that the values for the db fields are delineated by single quotes (desc='string in here'), so I only need to double up single quotes ' ', so actual double quotes, or quotation marks, are passed through without a problem - I don't need to worry about them going INTO the db, but I do need to fix them coming OUT of it. I first tried doubling up both single and double quotes as suggested, but mssql retains double double quotes ("") - it only filters out double single quotes ('').

 

Once it's in the db with the correct double and single quotes, I have to be careful when pulling it out and displaying it in HTML. So, when I read from the db, I run all user strings through htmlspecialchars:

 

function fnUnTick($string) {
$string = htmlspecialchars($string, ENT_QUOTES, 'UTF-8');
return $string;
}

 

Sure would be easier if all dbs had some other way to delineate their SQL - something like <mssql>select from...</mssql>

 

[kicken]

Sure would be easier if all dbs had some other way to delineate their SQL - something like <mssql>select from...</mssql>

They do, it's called a prepared statement.  You send the SQL and the data in separate streams so there is no need to escape it at all.

 

[benphp]

Nice. I'm not using PDO -just spent the day converting to sqlsrv that has the ability to prepare an array using execute. I'll look into that. Thanks!

[benphp]

Even if you prepare a statement, you still have to fix your apostrophes in the preparation. The delineation issue is more of a PHP puzzle than a db one.

[kicken]

Even if you prepare a statement, you still have to fix your apostrophes in the preparation. The delineation issue is more of a PHP puzzle than a db one.

 

No you don't.  If you use a prepared statement (properly) you don't have to do any kind of escaping what so ever.  When you do a prepared statement the data is sent separate from the query so there's no chance it will get mixed together and cause problems.