1

I'm building my query:

$q = sprintf("UPDATE testTable SET Text='%s', [Read]=0, TimeUpdated='%s', [From]='%s' WHERE ID='%s'", ms_escape_string($text), $dateReceived, $from, $convID);

and I execute it:

$res = mssql_query($q, $dbhandle);

$text should be free text so it could contain all sorts of weird characters (for now let's stick to ASCII). The simplest scenario is when $text contains a quote, e.g. $text = "Mc'Donalds"

Inside the ms_escape_string function I try to prevent this by replacing ' with 2 quotes ''. I echo the query string:

UPDATE testTable SET Text='Mc''Donalds', [Read]=0, TimeUpdated='2012-08-03 12:44:49', [From]='bogus' WHERE ID='14'

(Note: executing this query from the VS server explorer on the same db works just fine)

Everything seems ok - see the double quotes for Mc''Donalds - but it still fails when executing: [mssql_query(): message: Incorrect syntax near 'Mc'

I thought that SET QUOTED_IDENTIFIER might be the culprit so I tried 

 $q = "SET QUOTED_IDENTIFIER OFF";
 $resq = mssql_query($q,$dbhandle);

before executing my query but no cigar - I still get the same error.

Now I'm stuck - what should I change to get strings containing single quotes to pass through?

Community
  • 1
  • 1
Ando
  • 11,199
  • 2
  • 30
  • 46
  • 1
    **whoa.** i wouldn't use `sprintf` to generate a sql statement like that. – Daniel A. White Aug 03 '12 at 13:17
  • @DanielA.White Can you expand on that? As long as the arguments are properly escaped, I don't see an inherent problem with it, although I've never done it myself... – DaveRandom Aug 03 '12 at 13:19
  • its risky. and it also causes the OP's problem – Daniel A. White Aug 03 '12 at 13:21
  • @DanielA.White Fair enough, but can you explain *why*? – DaveRandom Aug 03 '12 at 13:22
  • @Ando I would (in general, not just in the face of the current problem) suggest you look at [sqlsrv](http://php.net/manual/en/book.sqlsrv.php) because `mssql_*` functions can be a total PITA and sqlsrv has easy support for prepared statements and has a PDO driver – DaveRandom Aug 03 '12 at 13:24
  • ok -it's not the best way of writing the query - other alternatives? (without using PDO) – Ando Aug 03 '12 at 13:25
  • @Ando - why no pdo? it would solve many problems. – Daniel A. White Aug 03 '12 at 13:26
  • now I'm switching to PDO (or sqlsrv) because I can't get around this quotes issue. Though the question is still valid- it should be possible with mssql_* - what's the problem? What am I missing? – Ando Aug 03 '12 at 13:28
  • 1. Do not use `mysql_` use summat like `mysqli` instead. 2. use `prepare` and `bind` set of functions. Lot less hassle. – Ed Heal Aug 03 '12 at 13:46

1 Answers1

1

This question seems more to do with the lack of a native mssql_real_escape_string() function, which is addressed by this thread.

You should be more worried about an SQL injection attack, a problem many of us have finally put to bed by preferring to use PDO, as has been mentioned in the comments.

This type of "Escaping in readiness for the next recipient of the data" forms part of the FIEO mantra (Filter Input Escape Output).

Community
  • 1
  • 1
Cups
  • 6,901
  • 3
  • 26
  • 30
  • I've read the thread before asking my question - the ms_escape_string is taken from there. Now I'm switching to PDO - but the question is still valid. – Ando Aug 03 '12 at 13:41
  • The comments under that code you pulled out suggest that solution may not be 100% solid and your OP seems to confirm it - sorry not to be able to add much more to the discussion then. – Cups Aug 03 '12 at 13:48