Inserting data using PHP into mysql when it contains a '

Question

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

I'm not a PHP guy or a MySQL guy, but can you do some sort of HTML ENCODE to encode it? Then you can HTML DECODE it to display it back again. — Chase Florell, Jun 04 '10 at 17:05
Ah.. the guru's say it's called `mysql_real_escape_string` - I think it's the same concept as html encoding — Chase Florell, Jun 04 '10 at 17:08
Why does `$description` have double quotes around it? Won't that cause PHP errors since they're not escaped? And MySQL errors if they are escaped? — Brendan Long, Jun 04 '10 at 17:42
solved with this code before the MySQL, thanks all! $link_id = mysql_real_escape_string($link_id); $link_name = mysql_real_escape_string($link_name); $description = mysql_real_escape_string($description); $metadesc = mysql_real_escape_string($metadesc); $link_created = mysql_real_escape_string($link_created); $link_modified = mysql_real_escape_string($link_modified); $website = mysql_real_escape_string($website); $cost = mysql_real_escape_string($cost); — kitenski, Jun 04 '10 at 17:44
NO NO NO NO. USE PREPARED STATEMENTS. Prepared statements are the only bullet proof method of preventing SQL injection attacks and you should simply not consider using anything else. Doing otherwise is just very poor professional practice. — Cruachan, Jun 04 '10 at 17:55
Also see question http://stackoverflow.com/questions/2353666/php-is-mysql-real-escape-string-sufficient-for-cleaning-user-input — Cruachan, Jun 04 '10 at 23:04

score 6 · Accepted Answer · answered Jun 04 '10 at 17:05

6

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

answered Jun 04 '10 at 17:05

Bob Fincheimer

17,978
1
29
54

Jeez NO. -1, despite all the plus votes. Real escape string is sticking plaster at best, the ONLY way of doing this properly is to use prepared statements. – Cruachan Jun 04 '10 at 17:44
*so make sure all of your values go through the mysql_real_escape_string function and you will be fine.* This statement is misleading and untrue. It's an improvement, but not a totally reliable solution. – Cruachan Jun 04 '10 at 23:06

score 1 · Answer 2 · answered Jun 04 '10 at 17:05

1

Just pass your data through mysql_real_escape_string()

answered Jun 04 '10 at 17:05

Fletcher Moore

13,558
11
40
58

2

To clarify, pass each value through that function before you create the query string. You don't use the function on the entire query. – Marcus Adams Jun 04 '10 at 17:10
No, use prepared statements. -1 for suggesting otherwise. – Cruachan Jun 04 '10 at 17:44

score 0 · Answer 3 · answered Jun 04 '10 at 17:41

0

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.

answered Jun 04 '10 at 17:41

mpen

272,448
266
850
1,236

Not a bad idea. What if their array already contains `,` though? – Brendan Long Jun 04 '10 at 17:47
@Brendan: What do you mean? If the values in the array already have trailing commas? They'll be quoted and inserted into the DB with the commas... who downvoted this? – mpen Jun 04 '10 at 17:57
I up-voted since someone had already voted this down (and I think it's a great idea). What I was asking about though, is what if they get this array: `{"x","y",","}`? Won't it be converted to: "x,y,,", then when it's turned back into an array: `{"x","y","",""}`? – Brendan Long Jun 04 '10 at 20:13
@Brendan: Well, no, it would be converted into `'x','y',','`. It wraps single quotes around each value (unless it's an integer or empty string). – mpen Jun 05 '10 at 05:02
The only place I've actually used the array bit is in a query like `SELECT * FROM table WHERE val IN (%s)`. – mpen Jun 05 '10 at 05:09

score 0 · Answer 4 · edited May 23 '17 at 12:13

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.

@Brendan, not much harder to use at all, especially if you use a wrapper. Frankly if anyone's coding is of a level where they're challenged by prepared statements they should give up and go and do something else. — Cruachan, Jun 04 '10 at 23:03

score -1 · Answer 5 · answered Jun 04 '10 at 17:06

-1

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!

answered Jun 04 '10 at 17:06

Zuul

16,217
6
61
88

`addslashes` doesn't protect against injection: http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string – Brendan Long Jun 04 '10 at 17:25

Inserting data using PHP into mysql when it contains a '

5 Answers5