[RESOLVED] syntax question

182x

hey guys I am using the following syntax to insert records into my database however if one of the records contains an apostrophe the sql statement will not work I was just wondering why and how to fix it? Thanks.

 $test="INSERT INTO test VALUES ('','{$testing['cId']}','{$testing['user']}')";

laserlight

Use prepared statements with the PDO extension (or mysqli, if you are using that). If not, use the appropriate escaping function such as mysql_real_escape_string().

182x

How would that function be applied into the query? Also is there any other characters that could cause problems with this type of insert? thanks

laserlight

How would that function be applied into the query?

Read the PHP manual on [man]mysql_real_escape_string/man. I would prefer prepared statements.

Also is there any other characters that could cause problems with this type of insert?

They should all be handled by the escaping function.

182x

So in this case would it be the following?

mysql_real_escape_string($testing['cId']),
            mysql_real_escape_string($testing['user');

Also if the escape string was not used would any other characters effect it? thanks.

NogDog

From the [man]mysql_real_escape_string/man page:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

182x

Thanks, in the example I posted above is that the correct syntax for the arrays?

NogDog

$test="INSERT INTO test VALUES ('','" . mysql_real_escape_string($testing['cId']) . "','" . mysql_real_escape_string($testing['user']) . "')";

Or you might find it more readable this way:

$test = sprintf(
   "INSERT INTO test VALUES ('', '%s', '%s')",
   mysql_real_escape_string($testing['cId']),
   mysql_real_escape_string($testing['user'])
);

182x

thanks 🙂

Does t his method of escaping apply to all SQL or only when I used syntax as in my first post? Thanks again

laserlight

Does t his method of escaping apply to all SQL or only when I used syntax as in my first post?

It should be applied to any data of external origin, e.g., that submitted from a form. The exception is when it is numeric and has been casted appropriately.

182x

Thanks again, I am a little confused now as I am just testing the code and in normal insert statements without the braces it seems to accept apostrophies without any problems, is this normal?