SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 48
  1. #1
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Escaping numerical values in MySQL

    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:

    PHP Code:
    $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.

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Probably the best way to prevent SQL injection is Prepared Statements?
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    Probably the best way to prevent SQL injection is Prepared Statements?
    Thanks but I'd be interested in knowing without using prepared statements.

  4. #4
    ¬.¬ shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Casting to integer or float removing everything but an integer or number. SQL injection is not possible from it.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks, logic_earth.

    Quote Originally Posted by felgall View Post
    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?

  7. #7
    ¬.¬ shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    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.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  8. #8
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by logic_earth View Post
    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";

  9. #9
    ¬.¬ shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    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.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  10. #10
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by logic_earth View Post
    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.

  11. #11
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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?

  12. #12
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    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.

  13. #13
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  14. #14
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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!

    Quote Originally Posted by felgall View Post
    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.

  15. #15
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  16. #16
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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.

  17. #17
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  18. #18
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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.

    Quote Originally Posted by felgall View Post
    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.

  19. #19
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,244
    Mentioned
    155 Post(s)
    Tagged
    0 Thread(s)
    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.

  20. #20
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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:

    Code PHP:
    // 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

  21. #21
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  22. #22
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    I would add one more to this list, simply validating the number:

    Code PHP:
    // 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?

  23. #23
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?

  24. #24
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    "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.

    Quote Originally Posted by DrQuincy View Post
    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:

    PHP Code:
    $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:

    PHP Code:
    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.


    Quote Originally Posted by DrQuincy View Post
    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 .

  25. #25
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    443
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •