0

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?

DJ_Beardsquirt
  • 289
  • 5
  • 14
  • Do you know who is giving that error? I think it’s MySQL. – Gumbo Jul 08 '14 at 18:20
  • 1
    So, I think you need to show us how you map excel to mysql. Without that we can't tell much. – Sebas Jul 08 '14 at 18:21
  • 1
    `mysql_*` functions are deprecated. Use `mysqli_*`. – rybo111 Jul 08 '14 at 18:22
  • 1
    We'll need to see some code. Expect some chiding if you're writing new code [using the mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). – Matt Gibson Jul 08 '14 at 18:24

1 Answers1

3

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) . "'";
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Whoops, yeah it seems like I had single quotes in my query (`"INSERT INTO orders VALUES (0, {$fname}, {$lname}, {$address1}, {$address2}, {$town}, {$county}, {$postcode}, {$country}, {$tel}, {$email}, '', {$postage}, {$subtotal}, {$total}, {$basket},'', 'eBay', now(), 'GBP', 'paypal', 1, '', 1, '{$txnId}') ON DUPLICATE KEY UPDATE txnid=txnid, complete=complete, new=new, time=time;"`) - the problem is that if I remove them I get invalid syntax near `'\"6Y8143371T918184S\"`, which is a txnid value. – DJ_Beardsquirt Jul 08 '14 at 18:53