# in SQL statement screwed up my DB!

[subwayman]

Just had a catastrophe in my database. Whilst updating a single record the whole dataset was updated. Eventually worked it out to be a '#' symbol in the data. For example the query should read:

UPDATE daily_sales SET grosssales = 1100.00 WHERE salesdate = '2007-04-16'

but due toi a validation failure the query was:

UPDATE daily_sales SET grosssales = 1100.00# WHERE salesdate = '2007-04-16'

Looked through some syntax sites for SQL and can't work out why the query should not have just failed rather than screw up all my records. Couldn't find anything on the mySQL site either. Anyone enlighten me?

Also, as an aside the value at fault is checked using for following php:

If (!ereg('-*([0-9]{1,4}).([0-9]{1,2})',$_POST[$fieldname])) {
$errorsfound = true;
$errorlist[$fieldname].='value should be in form 0.00<BR>';
}

How come it didn't throw an error?

Thanks

[Piranha]

I have no idea how this error could come up, but a guess is that MySQL thought that the query was finished when it saw the # sign, then setting all grosssales to the value specified. By using ' signs around all variables and checking all variables with mysql_real_escape_string you will avoid this problem, the query would then be:

UPDATE daily_sales SET grosssales = '1100.00#' WHERE salesdate = '2007-04-16'

I don't know what data type you have in grosssales, but I assume that it is some kind of numerical. If it is then that query would fail and you would get an error message. Read up on database injection or SQL injection to understand it better.

[subwayman]

I have finally found a bit in the mySQL manual. # is a precurser to a comment, so the rest of the current line is ignored. Lucky I keep backups . . .

Thanks.