CODEWITHSUNDEEP
mysqlsqlcasting
Luke Cousins
Luke Cousins

Reputation: 2156

Prevent mysql casting string to int in where clause

I'm trying to prevent mysql from casting a string to an int in a where clause.

The following query returns the correct row for the order

SELECT delivery_name FROM orders WHERE orders_id = '985225'

The following query also returns the same row as it is casting my string to an int, but I'd like to prevent it from returning the row at all (ie, not cast the string given to an int and effectively say, this cannot accurately be cast to an int so don't).

SELECT delivery_name FROM orders WHERE orders_id = '985225a'

I hope that makes sense, any help would be greatly appreciated!

Upvotes: 3

Views: 2192

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Step 1 - remove the single quotes from the sql string. In other words, change this:

WHERE orders_id = '985225'

to this:

WHERE orders_id = 985225

Step 2 - as per @Marc B's comment, ensure you actually have an integer.

Step 3 - Use a query parameter. MySql accepts them as far as I know.

Upvotes: 0

jean
jean

Reputation: 4350

I suggest you to pas that variable as a integer from your application.

SELECT delivery_name FROM orders WHERE orders_id = '985225'

If orders_id is a integer it means for every row in orders engine ill implict cast it to string to evaluate orders_id = '985225' its not just slow but ill render useless any index in orders_id.

if all you can do is edit the SQL i suggest you to cast the variable.

orders_id = CAST('985225' AS INT)

And one final hint, at application layer its more easy do validate input and prevent a non valid integer input from user.

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You should be able to avoid this scenario altogether, but if you wanted a workaround you could concat a character to the front of the string, something like:

SELECT delivery_name 
FROM orders 
WHERE CONCAT('a',orders_id) = CONCAT('a','985225a')

Upvotes: 1

Related Questions