Inserting data using PHP into mysql when it contains a '

Question

Hi there,

I am writing lots of info from an XML file into a database.

Everything works fine until I come across a field with the ' in the description, that insertion fails with an error

Error

1064:You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'd like you to feel that way too. We'd love to have you visit us to view over 100' at line 3

Is there a way to have this inserted without it failing? the import file could be large and change regularly so I cannot search and replace ' characters within it.

My actual PHP Statement is:

$query = mysql_query("REPLACE into list
(id, name, link, description, cost, date_added,type,myipaq,private,imgurl)
VALUES ('$id','$name','$link',"'$description'",'$cost','$date','$type','$myipaq','$private','$imgurl')");

thanks in advance,

Greg

Answer 1

Just pass your data through mysql_real_escape_string()

Answer 2

This falls under the category of SQL injection.

In PHP a function: mysql_real_escape_string is used to encode a string so that none of it can affect the SQL statement it might be concatenated into.

so make sure all of your values go through the mysql_real_escape_string function and you will be fine.

API REF: http://php.net/manual/en/function.mysql-real-escape-string.php

Answer 3

Use: php.net/manual/en/function.addslashes.php

Addslashes prevent's just that!

And if you use that, just use

http://www.php.net/manual/en/function.stripslashes.php

to remove slashes from your string!

Answer 4

Use my handy dandy function:

function mysql_safe_string($value) {
    if(is_numeric($value))      return $value;
    elseif(empty($value))       return 'NULL';
    elseif(is_string($value))   return '\''.mysql_real_escape_string($value).'\'';
    elseif(is_array($value))    return implode(',',array_map('mysql_safe_string',$value));
}

function mysql_safe_query($format) {
    $args = array_slice(func_get_args(),1);
    $args = array_map('mysql_safe_string',$args);
    $query = vsprintf($format,$args);
    $result = mysql_query($query);
    if($result === false) echo '<div class="mysql-error"><strong>Error: </strong>',mysql_error(),'<br/><strong>Query: </strong>',$query,'</div>';
    return $result;
}

Like so:

mysql_safe_query('INSERT INTO table VALUES (%s, %s, %s)', $val1, $val2, $val3);

And forget about quoting or not quoting your strings, and writing out mysql_real_escape_string a dozen times.

Answer 5

The only really safe way of inserting or replacing or indeed interacting with anything on a database with PHP is to use prepared statements. There really is no excuse anymore for doing it any other way. Escaping strings using mysql_real_escape_string will give you some protection, but it is not bullet proof.

Prepared statements are not even hard. See the PHP manual page on them, and there are several wrappers to make life even easier, personally I like the codesense mysqli wrapper a lot and have been using it for a while with no problems - it's no harder than straight MySQL PHP code. EasyPDO looks promising too.

You should check out the related question "PHP: Is mysql_real_escape_string" sufficient for cleaning user input" for further details as to why you shouldn't be lazy.