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:
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?
Is there any better way to escape numerical values in PHP (for int and floats respectively) or is casting sufficient?
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.
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.
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.
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.
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!
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.
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.
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.
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.
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.
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.
