Escaping numerical values in MySQL

[DrQuincy]

Hi,

I've always escaped numerical values in MySQL using real_escape_string but have just read that this could still lead to a SQL injection—and of course as the function name suggests it is for strings only, stupid me. I don't think any of my sites are vulnerable though as I'm pretty sure I cast ints when validating, for example, a page number on the front end. Example:

$page = (int) $_GET["page"]; 

My questions are:

1. How could not casting per above result in a SQL injection? Would the worst case scenario be they add =0 to the end and return all results?

2. Is there any better way to escape numerical values in PHP (for int and floats respectively) or is casting sufficient?

Thanks.

[siteguru]

Probably the best way to prevent SQL injection is Prepared Statements?

[DrQuincy]

Probably the best way to prevent SQL injection is Prepared Statements?

Thanks but I'd be interested in knowing without using prepared statements.

[logic_earth]

Casting to integer or float removing everything but an integer or number. SQL injection is not possible from it.

[felgall]

Using prepare/bind is the only guaranteed way to prevent injection as the alternative has the data and sql jumbled together. No matter how well you validate the data and escape it if necessary before jumbling it with the sql there is always the potential for someone to figure a way to bypass the validation and escaping.

If your numbers have been validated using is_numeric() prior to inserting into the sql then they are as safe from injection as possible without using prepare.

[DrQuincy]

Thanks, logic_earth.

No matter how well you validate the data and escape it if necessary before jumbling it with the sql there is always the potential for someone to figure a way to bypass the validation and escaping.

Thanks, I've heard that before but am confused as to how it can happen. Do you have an example?

Are both PDO and MySQLi prepared statements completely impervious to injections?

[logic_earth]

Are both PDO and MySQLi prepared statements completely impervious to injections?

Yes. Prepare Statements, send the SQL query and the data separately. The data never touches the SQL, so mater what the data contains it cannot cause the SQL to be malformed. However, be aware, if you take any part of user data to build the SQL query you will fall back into a potential for an injection.

[DrQuincy]

Yes. Prepare Statements, send the SQL state and the data separately. The data never touches the SQL, so mater what the data contains it cannot cause the SQL to be malformed. However, be aware, if you take any part of user data the build the SQL query you will fall back into a potential for an injection.

Thanks, by that do you mean something like:

$tablename = $_POST["tablename"];

$query = "SELECT * FROM $tablename";

[logic_earth]

Thanks, by that do you mean something like:

$tablename = $_POST["tablename"];

$query = "SELECT * FROM $tablename";

Exactly like that. There is still that potential for injection like normal even with prepared statements.

[DrQuincy]

Exactly like that. There is still that potential for injection like normal even with prepared statements.

Thanks, I would always treat that sort of user input with suspicion anyway.

[Lemon Juice]

Using prepare/bind is the only guaranteed way to prevent injection as the alternative has the data and sql jumbled together. No matter how well you validate the data and escape it if necessary before jumbling it with the sql there is always the potential for someone to figure a way to bypass the validation and escaping.

Can you elaborate more on that? How can sql injection happen if all data is escaped properly?

[DrQuincy]

Can you elaborate more on that? How can sql injection happen if all data is escaped properly?

I think I disagree with felgall on that also. The only way real_escape_string fails is if you use it on a numeric value (which it is not meant for). Apparently, if you don't set the charset properly there are a couple of charsets that have holes in them but they don't include utf8 or any of the latin ones.

[felgall]

I think I disagree with felgall on that also. The only way real_escape_string fails is if you use it on a numeric value (which it is not meant for).

If you use prepare/bind your SQL and data are completely separate and injection is impossible.

If you jumble them together and rely on real_escape_string to prevent injection then there is a possibility that injection could occur since the data and sql are still jumbled together. The vulnerability allowing the injection need not involve real_escape_string at all and would probably make use of a security hole elsewhere in your code.

[Lemon Juice]

If you jumble them together and rely on real_escape_string to prevent injection then there is a possibility that injection could occur since the data and sql are still jumbled together.

Well, but still you haven't explained how injection is going to happen when we rely on real_escape_string. I've never heard of that so I'd like to know, perhaps not only me!

The vulnerability allowing the injection need not involve real_escape_string at all and would probably make use of a security hole elsewhere in your code.

Well, if you say the injection need not involve real_escape_string then the same security hole can happen if you use prepared statements. We all know that if we are not careful security holes can appear anywhere in the code but that's not what we are talking about here.

[felgall]

Well, if you say the injection need not involve real_escape_string then the same security hole can happen if you use prepared statements. We all know that if we are not careful security holes can appear anywhere in the code but that's not what we are talking about here.

No it can't with prepare/bind the sql is kept in one room and the data in a completely different room so there is no possibility of the data being interpreted as sql. To have injection the data needs to be in the same room as the sql in order for there to be a possibility of it being interpreted as sql instead of as data. Keeping the two completely separate completely eliminates the possibility of the data being interpreted as sql. No matter what security holes might exist in your code the data is still in the data room and not the sql room and so will be processed as data. The worst that can happen is that you end up with the injected sql saved in the database as junk data.

Using real_escape_string the sql and data are still in the same room - therefore there is still a possibility of injection if there is a security hole somewhere in your code. Provided that you are validating all of the fields properly when they are first input and escaping anything that has the potential to be misinterpreted as sql then injection can't happen but that doesn't mean that the next time you apply a patch to the code to fix some error that you will not accidentally remove part of the security that is currently preventing injection. It is quite common that a quick patch to fix one security hole introduces one or two others elsewhere in the code. With real_escape_string you can never be 100% certain that there isn't a security hole that will allow injection - with prepare/bind injection is impossible and so you could remove all the validation and escaping of the data and injection still couldn't occur because prepare/bind doesn't rely on any other measures to 100% guarantee that injection is impossible.

[Lemon Juice]

Now it seems to me you are saying something different than a few posts earlier, or at least that's how I understood it. You first said that even if all data is escaped properly then there is possibility of sql injection and now you are basically saying that escaping can be the cause of sql injection because a programmer can forget to escape when changing code at some later point. Then it's not that real_escape_string is insecure itself but it may not be used properly or not used at all due to sloppy programming. But does preparing a statement really change the scenario so much? The programmer still needs to remember to bind values and may potentially forget to. This is more or less the same amount of work as having to remember to escape values.

And I wouldn't be so sure that binding values prevent from injections due to security holes. A security hole can be anything - even forgetting to bind a value or inserting a variable directly to sql.

Besides, when we use some database abstraction layer like active record, data mapper, etc. then the escaping or binding mechanism is hidden somewhere deep withing the library and we don't really need to remember about it so there's no possibility of forgetting it. Then there's really no security difference between prepared statements and real_escape_string.

[felgall]

Now it seems to me you are saying something different than a few posts earlier

No - what I have been saying (or at least attempting to say) all along is that prepare/bind is 100% secure by itself - it doesn't depend on you applying any security anywhere else in your code. Not using prepare/bind means that you are relying on all the security measures applied to the actual data to prevent it being misinterpreted as sql. Any security hole has the possibility of allowing sql injection.

Using real_escape_string correctly might be 99.9999999999999999999999999999999999% secure depending on you actually using it correctly - less secure if you somehow don't apply it completely correctly in all cases. Using prepare/bind is always 100% secure regardless of the rest of the code used - no matter what changes you make to the code outside of the prepare/bind there will never be a possibility of sql injection.

If you use real_escape_string and make a mistake in your code the result might be to allow injection. You can make as many mistakes as you like in code using prepare/bind and as long as you don't include any data fields in the prepare statement there will never be any possibility whatsoever of injection.

[Lemon Juice]

No - what I have been saying (or at least attempting to say) all along is that prepare/bind is 100% secure by itself - it doesn't depend on you applying any security anywhere else in your code.

All right, so at least I got part of your message right in that I also believe than prepare/bind is 100% secure and I don't doubt it. But I don't see how real_escape_string when used properly is less secure. You can say the same thing about real_escape_string - that you don't have to apply any security anywhere else in your code. In both cases you have to apply the security: in case 1 you have to do prepare/bind, in case 2 you have to do real_escape_string - both in more of less the same place and with the same amount of work.

Using real_escape_string correctly might be 99.9999999999999999999999999999999999% secure depending on you actually using it correctly - less secure if you somehow don't apply it completely correctly in all cases.

Haha, this sounds really funny if I try to take these percentages literally . If you say real_escape_string is that secure then I think it is secure enough for all cases and the 0.0000000000000000000000000000000001% uncertainty is not worth worrying about. It's like saying that storing passwords as hashes is insecure because there are collisions or someone could guess someone else's strong password.

I wouldn't worry so much about data being jumbled with sql as databases are designed to accept this kind of sql statements and be reliable at that - take for example sql backup dumps - they are nothing but jumbled sql with data and they work very well even with all kinds of binary junk that is stored in BLOB fields. Sure, theoretically a bug can happen in a database driver so that real_escape_string can be buggy but I think such cases are too rare to be worried about. So far, I don't see a reason why I would have to distrust a native escaping function.

[cpradio]

Okay, so I've read over this thread and since the specific question is about numerical values, I see a few possibilities (in no particular order)

1) PDO/MySQLi using prepared statements, is indeed secure.
2) Casting $number = (int)$_GET['myvalue'];
3) $number = $mysqli->real_escape_string($_GET['myvalue']);
4) $number = filter_var($_GET['myvalue'], FILTER_SANITIZE_NUMBER_INT);

There are probably more (including mysql_real_escape_string that could be utilized too, however, I believe it safe to say given any of the above methods, if you appended $number to your query, it could not be open to a SQL Injection.

[Lemon Juice]

1) PDO/MySQLi using prepared statements, is indeed secure.
2) Casting $number = (int)$_GET['myvalue'];
3) $number = $mysqli->real_escape_string($_GET['myvalue']);
4) $number = filter_var($_GET['myvalue'], FILTER_SANITIZE_NUMBER_INT);

I would add one more to this list, simply validating the number:

// 5)
if (!is_numeric($number)) {
  $number = 0;
}

This has the advantage in some edge cases where the number to be passed to SQL is outside of possible storage range in PHP:
- INT, BIGINT and DECIMAL numbers that exceed 32/64-bit
- large DOUBLE numbers
- even INT UNSIGNED may be too much for 64-bit PHP since PHP only supports signed integers, which makes the range 2x smaller for positive numbers

[felgall]

All right, so at least I got part of your message right in that I also believe than prepare/bind is 100% secure and I don't doubt it. But I don't see how real_escape_string when used properly is less secure.

"But I don't see how real_escape_string when used properly is less secure."

The text in bold is what makes the difference between the two. With prepare/bind you don't need to look at anything other than the prepare and bind calls to know whether it is 100% secure or not. With real_escape_string you need to look at the query and then track back all of the data references to where they were validated in order to see whether they might contain something that needs to be escaped and then examine the intervening code to confirm that those fields that need to be escaped are escaped.

Checking that real_escape_string is used properly can take several minutes as compared to the one second glance that checking prepare/bind requires - and that is assuming that you don't misread something during the checking - if you misread something then there might be a security hole that you have missed.

[DrQuincy]

I would add one more to this list, simply validating the number:

// 5)
if (!is_numeric($number)) {
  $number = 0;
}

This has the advantage in some edge cases where the number to be passed to SQL is outside of possible storage range in PHP:
- INT, BIGINT and DECIMAL numbers that exceed 32/64-bit
- large DOUBLE numbers
- even INT UNSIGNED may be too much for 64-bit PHP since PHP only supports signed integers, which makes the range 2x smaller for positive numbers

Aside: Could you elaborate on this? If a number is outside PHP's storage range, what happens?

[DrQuincy]

I see your point and actually think we're all agreeing with each other. Lemon Juice and I are saying that real escape is 100% secure if used on string. Your point is that you can execute a query and not run escape for whatever reason (you forget, code patch, whatever) whereas with prepared statements you must bind the params before executing so injection cannot occur.

To be pedantic you could “forget” to escape a dynamic table name generated from user input when using prepared statements and still suffer an injection.

I'm new to prepared statements. Out of interest what happens if you set the incorrect type in bind_param (E.g. i instead of s)? Does the query just fail?

[Lemon Juice]

"But I don't see how real_escape_string when used properly is less secure."

The text in bold is what makes the difference between the two. With prepare/bind you don't need to look at anything other than the prepare and bind calls to know whether it is 100% secure or not. With real_escape_string you need to look at the query and then track back all of the data references to where they were validated in order to see whether they might contain something that needs to be escaped and then examine the intervening code to confirm that those fields that need to be escaped are escaped.

Oh, now I see where our assumptions are different. When real_escape_string is used like you described above, that is *only* when necessary at all costs, then yes, it can become harder to spot security holes. But by proper use I mean escaping values always, no matter whether they come from a sanitized source or not. In such case there's no difference because we are doing the same amount of work - you have to make sure that all your variables are bound, I have to make sure that all of my variables are escaped. Therefore, I don't need to track my code back to where the values come from, I just know that all of them must be escaped right where I inject them into my query.

Aside: Could you elaborate on this? If a number is outside PHP's storage range, what happens?

Sorry, I wasn't clear enough, I was specifically commenting on this method:

$number = (int)$_GET['myvalue']; 

Imagine that myvalue is 18446744073709551610. This number is within mysql BIGINT UNSIGNED range but is too large for PHP to handle so after casting to INT this is what you will get on a 32-bit system:

2147483647

It will be larger on 64-bit systems but still the value will be changed because it is too large to be handled as a 64-bit signed integer, which PHP uses. Similar problems arise when you use $number = (float) $_GET['myvalue'];. Aside from the same problem of 'truncating' the number to another within the allowed range you take the risk of losing precision. Imagine you have this number:

1844674407370.123456789012

which mysql can store just fine in a DECIMAL field with perfect precision. PHP doesn't support precision decimal numbers so casting to a float is as good as you can get. But then the number will be changed to

1844674407370.1

Of course, PHP can handle such numbers just fine when you store them as strings so that is what you should do in such cases.

Also beware of filter_var - while the numbers won't be changed the filtering mechanisms are very dumb as they apply some very simple character removal. So a number like this will pass the filtering without problems:

filter_var('---184467440++++7370123456789012---', FILTER_SANITIZE_NUMBER_INT); 

Using such a number will result in SQL syntax error - you will prevent SQL injection in this way but having syntax errors is something you would probably also want to avoid. This is why I generally dislike the filter_ functions in PHP - while the idea is nice, the implementation is far from satisfactory...

When you sanitize your numbers with is_numeric() then you avoid such problems. Prepared statement should also work fine.

I see your point and actually think we're all agreeing with each other. Lemon Juice and I are saying that real escape is 100% secure if used on string. Your point is that you can execute a query and not run escape for whatever reason (you forget, code patch, whatever) whereas with prepared statements you must bind the params before executing so injection cannot occur.

To be pedantic you could “forget” to escape a dynamic table name generated from user input when using prepared statements and still suffer an injection.

Yes, it basically comes to coding standards and for security reasons you always have to do something with the values, whether you are binding them or escaping. When you do that always then you are safe.

Moreover, I believe that prepared statements are not the right tool for avoiding sql injections. While they have this nice side effect I believe it is a kind of workaround when they are used only for that reason. I can see a few downsides of using prepared statements in this manner - but maybe I'm going too much off-topic here .

[DrQuincy]

Thanks, so I guess is_numeric is the way to go then.

Do you see prepared statements as only useful if you are reusing the queries then?