Comparison data of different types is a complex topic. Final results are different for different languages. Today, my co-worker René noticed "interesting" behavior for comparison in SQL. It's so interesting that I've decided to share it with you :)
Firstly, have a look on results of comparisons in PHP.
echo 7 == '7'; // true
echo 7 == '7.1'; // false
echo 7 == '7;5'; // false
Results look like developers could expect. Now, have a look on the same comparisons in SQL.
SELECT 7 = '7'; # true
SELECT 7 = '7.1'; # false
SELECT 7 = '7;5'; # true(!)
SELECT 7 = '7 5'; # true
SELECT 7.5 = '7.5,900'; # true
Taadaa! I'm pretty sure that a lot of developers expected other results. It has important implication when you have the comparison like above in the WHERE
closure. Let's try some examples.
SELECT * FROM pages WHERE id = '1,5';
SELECT * FROM pages WHERE id = '2;7;8;9';
This behaviour might be potentially dangerous. If you set wrong data inside WHERE
, the query might return data which shouldn't be visible for a user,