CODEWITHSUNDEEP
phpmysqlinsertspecial-characters
kitenski
kitenski

Reputation: 639

Inserting data using PHP into mysql when it contains a '

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')");

Upvotes: 1

Views: 1254

Answers (5)

Cruachan
Cruachan

Reputation: 15971

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.

Upvotes: 0

mpen
mpen

Reputation: 282825

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.

Upvotes: 0

Zuul
Zuul

Reputation: 16269

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!

Upvotes: -1

Bob Fincheimer
Bob Fincheimer

Reputation: 18036

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

Upvotes: 6

Fletcher Moore
Fletcher Moore

Reputation: 13804

Just pass your data through mysql_real_escape_string()

Upvotes: 1

Related Questions