Posted almost 11 years ago. Visible to the public.

In MySQL, a zero number equals any string

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

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:

Copy
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.

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

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

Avatar
Andreas Robecke
Last edit:
about 2 months ago
by Henning Koch
Keywords:
conversion, comparison
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Andreas Robecke to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more