Comparison integer to string in SQL queries

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,


Blog Comments powered by Disqus.

Older Post