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?

Improve this question
4
  • Do you know who is giving that error? I think it’s MySQL. Commented Jul 8, 2014 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. Commented Jul 8, 2014 at 18:21
  • 1
    mysql_* functions are deprecated. Use mysqli_*. Commented Jul 8, 2014 at 18:22
  • 1
    We'll need to see some code. Expect some chiding if you're writing new code using the mysql_* functions. Commented Jul 8, 2014 at 18:24

1 Answer 1

Reset to default
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) . "'";
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.