为什么mysql_real_escape_string在不应该的地方插入引号？

Question

I have a php page that I use to import orders from a csv file into a mysql database. One varchar in the table is used for the txnid. I discovered that occasionally I'll come across a txnid that results in an "Illegal double value found during parsing" error. For example:

6L831105EP011602

is a fine value that doesn't cause any trouble.

71V72876YH791094

Again, no problems.

546635980E5898057

But this one causes the error, presumably because it is being parsed as a double.

I have tried using mysql_real_escape_string to sanitize the data, but this results in all values being prefixed with '' unless they would've been parsed as a double.

What is the correct way I should be sanitizing this data? How can I determine if a value is going to cause a parse error before I try to insert it into MySQL?

Answer 1

mysql_real_escape_string() does NOT add quotes to a string. It ESCAPES sql metacharacters.

e.g.

$str1 = "Miles O'Brien";
$str2 = "546635980E5898057";
$str3 = 42;

echo mysql_real_escape_string($str1);   // output: Miles O\'Brien
echo mysql_real_escape_string($str2);   // output: 546635980E5898057
echo mysql_real_escape_string($str3);   // output: 42

in no case would ANY escaped string suddenly come out like 'Miles O\'Brien' unless you added those quotes yourself. e.g.

echo "'" . mysql_real_escape_string($str1) . "'";