Jump to content

Archived

This topic is now archived and is closed to further replies.

benphp

Best way to handle double quotes? "How?"

Recommended Posts

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''

 

 

Share this post


Link to post
Share on other sites

If your not using PDO with MSSQL you should be.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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>

 

:D

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.