CODEWITHSUNDEEP
mysqlsqlstringinteger
user12097954
user12097954

Reputation:

SQL where comparison with number and string

I found that if I’m using a WHERE on an int column but have my condition as a number and string the row is still returned.

SELECT * FROM articles where post_id = '1-hey';

The above is an example more than anything. But why is the article returned? Is there some protection I should be doing? I don’t understand why row where post_id is 1 gets returned.

Upvotes: 0

Views: 720

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

My guess is that you're using MySQL here, which has some fairly complex casting rules which cover the sort of implicit casting which is happening in the WHERE clause of your query. In MySQL, if an integer column on the LHS is compared against a string on the RHS, there is a rule which says that as many characters as possible will be retained from the string such as can form an integer. Or, stated more simply, MySQL will look for numbers from the start of the string. Finding only 1, it will then compare against this value. So, the actual query which is being executed looks like:

SELECT * FROM articles where post_id = 1;

Best practice is to not even attempt this. On most other databases, your query would not even compile. If you really have the need to do this comparison, then consider either making the post_id text, or instead compare against only pure integers.

Upvotes: 2

Related Questions