mysql_real_escape_string & slashes

Firstly magic quotes & runtime are disabled correctly in php.ini, and confirmed by phpinfo().

PHP version: 5.3.4
MySQL version: 5.1.52

I'm only using mysql_real_escape_string on the data, after htmlspecialchars and a trim, that's all the data cleaning on the variable.

Yet, when I submit a single quote, the slash remains in the database.

When running mysql_query I'm using "' . $var . '", although in the past this hasn't changed anything (could be due to the double quotes?).

Any ideas开发者_JAVA百科? and please don't tell me about PDO/prepared statements, I'm aware of them and I have my reasons for doing it this way.

Thanks!

Code example (this is the only thing done to the data):

mysql_real_escape_string( htmlspecialchars( trim( $data ) ) );

I'm only use mysql_real_escape_string on the data, after htmlspecialchars and a trim, that's all the data cleaning on the variable.

No. Only use mysql_real_escape_string for storing data in the database. Don't mangle your data when you store it.

The function htmlspecialchars is used to encode a string to HTML (< becomes < etc.) and it should only be used for this purpose.

Perhaps the massively misguided, unhelpful and damaging option

magic_quotes_gpc

Has been enabled?

You can check that in the output of phpinfo(), but there's not a lot you can do if the server admin has enabled it globally without the ability to overrride.

I recommend checking if it's on (on every page of the app of course), and if so, causing the application to die quickly and painfully to ensure that you avoid data corruption (which chiefly manifests itself as the proliferation of backslashes you described).

Then go around to the server admin's house with a blunt weapon of your choice.

Hopefully you can do all this before your database becomes overrun with hoards of evil self-multiplying backslashes.

your storing procedure is correct. (altough htmlspecialchars and/or trim is probably not needed - but i dunno about your application)

from the information you are providing there is no reason to be seen for your problem.

the next debugging approach would then be remembering whatever else you may changed or has been changed on your system (maby you are using some 3rd party installation image?).

if that fails ie is left to wild guessing possible causes, for which i will offer a first one:

mysql could be running in NO_BACKSLASH_ESCAPES -mode, which would cause backlashes to be treated as regular characters.

furthermore it looks like you are wrapping your strings in double quotes, which would then insert a single quote - which usually gets escaped - straight into your database, preceded by a backslash.

it may very likely be also possible that - as you are wrapping your strings with double quotes inside your sql statements, which is not how it should be like and i am baffled you dont get a syntax violation error, you end up with some query like "john\'s house" which is caused by the single quote escaping from mysql_real_escape, which would be correct if you had your query correctly wrapped by single quotes.

which leads me to the question. do you get a syntax error (or an injected query) when trying to insert double quotes?

as for your comment. you could very well prepare statements with pdo and, then get the query string form it, and execute them using mysql functions. however i realise that this is no solution to your problem.

please also try putting your whole query in only one variable and print that out directly before executing it. then have a look at it and follow any wrong manipulation back operation by operation that is done to produce the string.

If you use double quotes within the SQL commands after escaping the data:

 SELECT "1\'2"

then it will store and return the value as 1\'2 with the backslash still intact.

The proper syntax for SQL strings is using single quotes. That's what mysql_real_escape_string is escaping for. Else it would have to escape double quotes, whose usage however it is completely unaware of.

Use double quotes in PHP. Use single quotes for SQL. Rewrite your code like that:

 $x = escapy($x);
 $y = escapy($y);
 sql_query("INSERT INTO tbl (x,y) VALUES ('$x', '$y')");