Inserting strings into MySQL

Question

I am running into a problem where I think my insert statement in MySQL is messing up the strings that get entered into the database.

I have an insert statement like this in PHP code:

$sql = 'insert into my_table ( numeric_id , string_value ) 
        values ( '.$some_number.' , "'.$some_text.'" )';

And when later I get the $some_text from the database, it messes up strings like don\'t instead of don't and ads things like \r\n to the output.

Any idea why this is happening and what I should change?

Answer 1

this should work

$sql = "insert into my_table ( numeric_id , string_value ) 
        values ( '.$some_number.' , '".$some_text."' )";

Answer 2

First of all, escape your input:

$sql = 'insert into my_table ( numeric_id , string_value ) values (' . mysql_real_escape_string($some_number) . ', "' . mysql_real_escape_string($some_text) . '")';

Second, the issue with the slash is likely due to PHP Magic Quotes. You can read more about that here: http://www.php.net/manual/en/security.magicquotes.disabling.php

You can check if magic quotes is turned on by running this:

var_dump(get_magic_quotes_gpc());

If it's on, you could either disable it (if you have access to php.ini) or you can use PHP code to fix the problem that magic quotes creates:

if (get_magic_quotes_gpc()) {
    function stripslashes_gpc(&$value) {
        $value = stripslashes($value);
    }
    array_walk_recursive($_GET, 'stripslashes_gpc');
    array_walk_recursive($_POST, 'stripslashes_gpc');
    array_walk_recursive($_COOKIE, 'stripslashes_gpc');
    array_walk_recursive($_REQUEST, 'stripslashes_gpc');
}

(taken from PHP.net)

Answer 3

Always use prepared statements to interpolate data into SQL. Then you don't have to do any escaping at all.

Answer 4

$sql = "insert into my_table (numeric_id, string_value) values ('$some_number' , '$some_text')"; $query = mysql_query($sql);

/** just use (") instead of ('); */

Answer 5

Some of your code is doing escaping twice.
You just have to find the code that does it second time and get rid of it.

first of all you have to print out your variables to see it's actual contents.
It's hard as hell to sort out things being blinded and based on assumptions.

Just print out $some_text variable before escaping it and see. if it's already escaped - then additional escaping been done somewhere earlier in the code.