0
DELETE FROM stats WHERE name=0

"name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were actually equal to "0".

Why did this succeed in such cases as: "Mike"=0

Thankfully this was done on a personal development server and not production! I surely want to avoid this happening in the future.

2 Answers 2

0

When a string (VARCHAR) is compared to a number (0), any digits at the beginning of the string are converted to a number. If there are no digits (eg, 'Mike'), the numeric value is 0.

WHERE 'Mike' = 0 -- succeeds
WHERE 'Mike' = '0' -- fails

Coding like that cannot be automatically prevented. This is part of debugging your application.

Similarly, if you use an INT in a string context (eg, CONCAT()), the INT value will be converted to a string.

0

This is how mysql (other RDMS do it in a different way) performs implicit string to number conversion . Using bind variables and proper setters in the applications should prevent such behavior.
For instance,

//pseudo-code , depends on the programming language/framework you use
PreparedStatement st = conn.prepareStatement("DELETE FROM stats WHERE name = ?");
st.setString(1,"0"); 
st.executeUpdate();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.