Problems with mysql insert

Question

My php script won't work if i try to insert into database something in Saxon genitive (for example value "mike's" won't be inserted). PHP code is plain and simple:

"INSERT INTO cache (id,name,LinkID,number,TChecked) VALUES(".$idUser.",'".$LinkName."',".$LinkID.",".$number.",NOW());"

Everything works great until "$LinkaName" get some value with "special character". How to put values like "mike's", "won't" etc. into MySql database?

Answer 1

You need to escape these strings properly. In addition, the technique that you're using right now exposes you to an SQL injection attack.

The PHP docs for mysql_real_escape_string gives a good example of what you should do:

// Query
$query = sprintf("INSERT INTO cache (id,name,LinkID,number,TChecked) VALUES(%d,'%s',%d,%d,'%s');",
   mysql_real_escape_string($idUser),
   mysql_real_escape_string($LinkName),
   mysql_real_escape_string($LinkID),
   mysql_real_escape_string($number),
   mysql_real_escape_string(NOW()));

Answer 2

You must escape them first, otherwise you generate an invalid query. The single quote matches the single quote at the start of the string.

$LinkName = mysql_real_escape_string($LinkName);

You can also use prepared statements to bind parameters to the query instead of concatenating and sending a string (use the PDO or mysqli libraries instead of the mysql lib).

Answer 3

You need to use mysql_real_escape_string() on those values.

Also make sure if you are not quoting those other variables, to cast them to integer (the only reason why you wouldn't quote them).

Answer 4

If you're using mysqli or PDO and not the standard extension, you can use a prepared statement instead of escaping.