In MySQL comparing zero to a string 0 = "any string" is always true!

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

• We build reliable cloud solutions with Infrastructure as code
• We are experts in security, Linux and databases
• We support your dev team to perform

Read more Show archive.org snapshot

So when you want to compare a string with a value of an integer column, you have to cast your integer value into a string like follows:

SELECT * from posts WHERE CAST(posts.comments_count AS CHAR) = '200' 

Of course this is usually not what you want to use for selecting your data as this might cause some expensive database operations. No indexes can be used and a full table scan will always be triggered.

If possible, cast the compared value in your application to avoid rewriting all lines in the database. ActiveRecord does that automagically, for example.

PostgreSQL is not affected

PostgreSQL will throw an error when you compare an integer with a string without explicit casting.

Related security issue

In authentication, this behavior may be used to match rows without knowing a secret token:

• Potential Query Manipulation with Common Rails Practises Show archive.org snapshot
• CVE-2013-3211 Show archive.org snapshot
• MySQL madness and Rails