MYSQL/PHP - Protect against automatic cast/convert a string to a number

Question

I have an orders table with order_id int(11) as PK.

A user enters the order_id and I pass it to MySql via

... WHERE order_id = ".db_input($order_id)."

The resulting SQL is as follows:

SELECT * 
FROM oc_order 
WHERE `order_id` = '16garbage'

It returns the record of order number 16!

WHY is it doing this? '16garbage' can NEVER be equal to integer 16!

How to fix this?

UPDATE #1: this has nothing to do with db_input. Forget that. It has everything to do with the EQUALITY order_id = '16garbage'.

As Raymond Nijland said below:

"...query internally runs as select * from oc_order WHERE order_id= CAST('16garbage' AS UNSIGNED) because of the auto datatype casting."

Let me geuss you are using `mysqli_real_escape_string()` on int datatypes ? I assume `order_id` to be a `INT` in the table.. Like the name `mysqli_real_escape_**string**` says it is mean to be used on strings **ONLY**, to SQL protect you should really use prepared statements... — Raymond Nijland, Oct 10 '19 at 10:28
db_input must be used because the data is coming from user input. However the problem is deeper than that. Even cast('16garbage' AS UNSIGNED) returns 16!! — conanDrum, Oct 10 '19 at 10:32
If your order_id is ```int``` then why are you comparing it with ```string```? — CaffeinatedCod3r, Oct 10 '19 at 10:32
The why ... -> [this](https://www.db-fiddle.com/f/3x6TwpqT9USje737WyAg7g/1) happens on run time as order by is a INT .. So your qeury internally runs as `select * from oc_order WHERE `order_id`= CAST('16garbage' AS UNSIGNED)` because of the auto datatype casting. — Raymond Nijland, Oct 10 '19 at 10:33
Like i said before to fully SQL protect and to prevent auto datatype casting and walking into this error you should really use prepared statements instead.. — Raymond Nijland, Oct 10 '19 at 10:34
Why downvote a legitimate question people? Don't you want people to learn? OMG! Please undo your downvote. — conanDrum, Oct 10 '19 at 10:34
So I guess, the way to fix this is to check in PHP, if db_input($order_id)==$order_id, and if not the same then make $order_id=''. What do you think? — conanDrum, Oct 10 '19 at 10:37
Yes, this is how MySQL, PHP, SQLite etc work. That’s why it’s important to write proper code and handle types well yourself. Many ways to shoot yourself in the foot and MySQL will not want you. — Sami Kuhmonen, Oct 10 '19 at 10:38
i didn't downvote.. Anywhy i am sure this question is asked (alot) before on stackoverflow which is most likely the reason somebody voted you down.. After a quick search i found -> [MySQL automatically cast/convert a string to a number?](https://stackoverflow.com/questions/21762075/mysql-automatically-cast-convert-a-string-to-a-number) — Raymond Nijland, Oct 10 '19 at 10:48

conanDrum · Accepted Answer · 2019-10-10T12:03:42.423

The only way to avoid this is:
If you already know that your table's order_id is type INT, then we have to make sure that when $order_id is converted to INT, it corresponds to the user input!

if ((strval((int)$order_id)) !== $order_id) {$order_id='';}

The command converts $order_id to int and then back to string and checks if the resulting string is the exact string the user has provided.
If not, then we know that the automatic conversion has taken place and corrupted the user input.
In such case, we destroy the user input so that it does not return data not destined for this user.

If we add this before we construct our SQL:
if the user enters '16garbage', $order_id will become ''.
if the user enters '16', $order_id will remain '16'.

MYSQL/PHP - Protect against automatic cast/convert a string to a number

1 Answers1