Insert into table SET - rows with special characters skipped

Question

I have this query:

$sql = " INSERT INTO table SET name = '$name', sku = '$number', description = '$desc' ";

But the rows containing some special characters (in my case this ') are not inserted.. How I can solve? Thanks in advance.

I'm assuming you don't have any error reporting, because MySQL doesn't "skip" things. It might fail silently without you knowing. Let it out! Your db class will have a method for the error message. Using PDO or MySQLi? — Rudie, Aug 01 '15 at 16:07

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

You must use parameterised queries instead of manually appending those values. Currently if name, number or description would contain any sql it would get executed.

A lot more detailed answer is in How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:44

Community

1
1

answered Aug 01 '15 at 16:04

Andrius

13
4

good point! +1 from me for that tip. the OP really should look into avoiding SQL injection – luksch Aug 01 '15 at 17:13

score 1 · Accepted Answer · answered Aug 01 '15 at 17:32

When you construct your query, you need to escape the data you are inserting. You need to at least use addslashes() function in PHP, like this:

$sql = "INSERT INTO table SET name = '".addslashes($name)."', sku = '".addslashes($number)."', description = '".addslashes($desc)."'";

However more correct way is to use a different function than addslashes, which would properly handle all characters in the data, not only apostrophes.

I am using my custom 'escape' function like this:

function escape($text)
{
   return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $text);
}

So using this function, you would write:

$sql = "INSERT INTO table SET name = '".escape($name)."', sku = '".escape($number)."', description = '".escape($desc)."'";

score 0 · Answer 3 · answered Aug 01 '15 at 15:16

0

Read about escaping characters in mysql. I think it is done with \

answered Aug 01 '15 at 15:16

luksch

11,497
6
38
53

Yes.. it works.. it's well to use with all the $data? – Gennaro Santoro Aug 01 '15 at 15:47
should be fine. in Mysql you can also use two ': ```''``` – luksch Aug 01 '15 at 15:52
Read from xml with simplexml_load_file – Gennaro Santoro Aug 01 '15 at 16:10

Insert into table SET - rows with special characters skipped

3 Answers3