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 orde

Solution 1:

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')

Solution 2:

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.

Solution 3:

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

WHEREorders_id='985225'

to this:

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