SitePoint Sponsor

User Tag List

Results 1 to 25 of 48

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    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,629
    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
    426
    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,784
    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
    426
    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
    426
    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 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?

  11. #11
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    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.

  12. #12
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,784
    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="^$">

  13. #13
    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.

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,050
    Mentioned
    152 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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  15. #15
    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

  16. #16
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    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?

  17. #17
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,244
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)
    Hi, folks. Sorry I'm late to the party. I wanted to ask about the original issue.

    Quote Originally Posted by DrQuincy View Post
    ...have just read that [escaped numerical values] could still lead to a SQL injection...
    Has anyone provided a testable and repeatable demonstration of this? Because if your values -- numeric or otherwise -- are quoted and escaped, then the vulnerability isn't at all obvious to me.
    "First make it work. Then make it better."

  18. #18
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    Hi, folks. Sorry I'm late to the party. I wanted to ask about the original issue.



    Has anyone provided a testable and repeatable demonstration of this? Because if your values -- numeric or otherwise -- are quoted and escaped, then the vulnerability isn't at all obvious to me.
    While you could quote and escape a numerical as MySQL will convert it to a number if it can, I would consider this bad practice—and doesn't it prevent indexing doing it this way?

    The example I read would go something like:

    $safe = mysql_real_escape_string($_GET['page']);

    In this case $_GET['page'] = "0 = 0"

    So the query could be:

    SELECT ... WHERE somefield = 0 = 0

    Which would return every record.

  19. #19
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,244
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    While you could quote and escape a numerical as MySQL will convert it to a number if it can, I would consider this bad practice—and doesn't it prevent indexing doing it this way?
    Either PHP or MySQL will eventually have to convert the value to a number. Is it so bad if we let MySQL handle that job? It comes with the added benefit that you can escape those values just like any other value.

    I don't see how this would prevent indexing.
    "First make it work. Then make it better."

  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 DrQuincy View Post
    While you could quote and escape a numerical as MySQL will convert it to a number if it can, I would consider this bad practice—and doesn't it prevent indexing doing it this way?
    It shouldn't prevent indexing but again I've come across some edge cases involving very large numbers where quotes actually changed what the query did, unfortunately I can't remember the details. Quoting numbers is non-standard and although nothing wrong should happen in mysql I prefer to always use them unquoted. MySQLi's real_escape_string() is very basic and not very universal so what I do is I use my own escaping function that can handle strings (even those from objects with __toString()), numbers, NULLs and also arrays for IN() clause. Actually, this is a method in an extended MySQLi class:
    PHP Code:
    class Db extends mysqli {
        
    /**
         * Escape value for sql query
         * @param mixed $value
         * @param string $type 's'=string, 'n'=number, null=automatic detection based on $value PHP type
         * @return string 
         */
        
    public function quote($value$type null) {
            if (
    $value === null) {
                return 
    "NULL";
                
            } elseif (
    is_array($value)) {
                
    // comma-separated list of values for IN()
                
    $items = array();
                
                foreach (
    $value as $item) {
                    if (
    $item === null) {
                        
    $items[] = "NULL";
                    } elseif (
    $type === "s" || ($type === null && (is_string($item) || is_bool($value)))) {
                        
    // string
                        
    $items[] = "'"$this->real_escape_string($item). "'";
                        
                    } elseif (
    $type === "n") {
                        
    // number
                        
    if (is_numeric($item)) {
                            
    $items[] = $item;
                        } elseif (
    is_object($item)) {
                            
    $items[] = (float) $item;
                        } elseif (
    $item === true) {
                            
    $items[] = '1';
                        } else {
                            
    $items[] = '0';
                        }
                    
                    } elseif (
    $type === null && (is_int($item) || is_float($item))) {
                        
    // auto-detected number
                        
    $items[] = $item;

                    } else {
                        throw new 
    Exception("Db: Wrong IN() parameters for quote()");
                    }
                }
                
                return 
    implode(","$items);
            
            } elseif (
    $type === "s" || ($type === null && (is_string($value) || is_bool($value) || is_object($value)))) {
                
    // string
                
    return "'"$this->real_escape_string($value). "'";
                
            } elseif (
    $type === "n") {
                
    // number
                
    if (is_numeric($value)) {
                    return 
    $value;
                } elseif (
    is_object($value)) {
                    return (float) 
    $value;
                } elseif (
    $value === true) {
                    return 
    '1';
                } else {
                    return 
    '0';
                }
                
            } elseif (
    $type === null && (is_int($value) || is_float($value))) {
                
    // auto-detected number
                
    return $value;
            }
                
            throw new 
    Exception("Db: Wrong parameters for quote(): $value,$type");
        }    

    This may not be exactly what you want so take is as an inspiration. For PDO a similar method could be constructed.

    Quote Originally Posted by DrQuincy View Post
    You've got me questioning whether I should moved to prepared statements now! I tend to agree with everything you've said. With MySQLi is there anyway to get the actual query executed as a string in case of error? If you can't I would have this down as a disadvantage since logging the query upon error is very useful for debugging.
    I don't think you can get the query with data either in Mysqli or in PDO. Even MySQL slow query log will not show the data, as far as I know. You'd have to construct your own mechanisms that would capture data from all bind...() calls along with the prepared statement and reconstruct the query by replacing the placeholders with their corresponding values. While certainly feasible, I think it's too complicated to bother doing this.

  21. #21
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I meant you can do:

    $query = "SELECT * FROM `table`";
    $result = mysqli_query($link, $query);

    if(!$result) {

    log_query($query); // This just writes $query to a text file

    }

    My point was if you using prepared statements and aren't storing the query as a string as above, can you just as easily log the error?

  22. #22
    ¬.¬ shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Try using:
    http://www.php.net/manual/en/mysqli-...eport-mode.php
    http://www.php.net/manual/en/class.m...-exception.php

    It should give more information when there is an error. Including the failing SQL query. Maybe.

    However, you know. If you don't store the query even if you use the old interface there is no way to get the query unless you have it saved in a variable. But using the exceptions should provide some insight.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  23. #23
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,244
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)
    This might be one of the more confusing analogies I've ever read.

    I'm a guy who favors prepared statements, but I think I agree with Lemon and the Dr on this one. I agree that it's easier to make mistakes with escaping than with preparing, but I also agree that if both are used properly, then both are equally secure.
    "First make it work. Then make it better."

  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 Jeff Mott View Post
    This might be one of the more confusing analogies I've ever read.
    Haha, this wasn't easy but after reading it for a second time I think I grasped the gist more or less

    Quote Originally Posted by Jeff Mott View Post
    I'm a guy who favors prepared statements, but I think I agree with Lemon and the Dr on this one. I agree that it's easier to make mistakes with escaping than with preparing, but I also agree that if both are used properly, then both are equally secure.
    Exactly, it's a little bit easier to keep good security discipline when coding binding than escaping but I don't think the difference is big. It's more because of some of the trade-offs of prepared statements that I mentioned earlier that I tend to favour escaping.

    But it's all down to personal preference. Some people prefer the idea of wrapping up all presents in a paper bag, putting them on table and then getting two guys move the whole table with presents from one room to another, whereas some prefer to mark the spots on the table where each present should be placed and then getting the guys move the table first and come back and get the presents separately and leaving it up to a lady in the other room to place the presents in their proper places marked on the table .


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
  •