#1
  1. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014

    Exclamation PDO security issues


    Hi,

    many of us (including myself) recommend PDO and prepared statements as a secure solution for database queries. If you do some reading, however, it turns out that's not the case:
    • PHP versions before 5.3.6 do not support the "charset" attribute in the MySQL DSN, so you're forced to query the database with SET NAMES (the manual actually recommends this).
    • However, PDO's escaping function is not aware of SET NAMES, so it will assume the default encoding (usually latin1). This will render the escaping useless in some encodings (like GBK).
    • PDO doesn't use actual prepared statements by default. Instead, it takes the input parameters, escapes them and inserts them into the query string before sending the whole thing to the database. This will break as well if you use SET NAMES


    Check this example:
    Code:
    CREATE TABLE users (
    	user_id INT PRIMARY KEY AUTO_INCREMENT
    	, email_address VARCHAR(255) NOT NULL
    	, password VARCHAR(255) NOT NULL
    ) CHARACTER SET GBK
    ;
    
    INSERT INTO users
    SET
    	email_address = 'test@test.com'
    	, password = '123456'
    ;
    PHP Code:
    $db_options = array(
        
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES GBK'        // bad idea!
    );
    $database = new PDO('mysql:host=localhost;dbname=test''user''password'$db_options);

    // standard attack on GBK:
    // If the escaping function uses latin1, 0xBF27 will become ' in GBK, so you can inject a single quote
    $email_input chr(0xBF) . chr(0x27) . ' OR 1 = 1 -- ';
    $password_input '';

    $user_stmt $database->prepare('
        SELECT
            user_id
            , email_address
        FROM
            users
        WHERE
            email_address = :email_address AND password = :password
    '
    );
    /*
    Thanks to SET NAMES and emulated "prepared statements", the query sent to
    the database system will be this:

    SELECT
            user_id
            , email_address
        FROM
            users
        WHERE
            email_address = '' OR 1 = 1 -- ' AND password = ''
    */
    $user_stmt->execute(array(
        
    ':email_address' => $email_input
        
    ':password' => $password_input
    ));

    // finds all users instead of none
    var_dump$user_stmt->rowCount() ); 
    To avoid those issues:
    • Never use SET NAMES, always specify the character encoding in the DSN. On PHP < 5.3.6, there is no safe way to change the character encoding on client side. If you need another encoding than latin1, change the MySQL configuration on the server.
    • Set PDO::ATTR_EMULATE_PREPARES to false, so that you'll get actual prepared statements instead of "emulated" ones, which can be vulnerable to escaping issues


    For example:
    PHP Code:
    $db_options = array(
        
    PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements)
        
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent)
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays)
    );
    $database = new PDO('mysql:host=localhost;dbname=YOURDB;charset=utf8''YOURUSER''YOURPW'$db_options);    // important! specify the character encoding in the DSN string, don't use SET NAMES 

    Comments on this post

    • E-Oreo agrees
    Last edited by Jacques1; January 27th, 2013 at 01:19 PM.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Good examples. I've stickied this for now. Once any initial discussion in the thread dies out I'll move it into the FAQs forum.

    If I understand correctly, there are no security implications for UTF-8, although the potential for data corruption exists.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Great post!!

    Just wondering though, your recommendation to never use set names, is this also true for mysqli->prepare? I tend to use this instead of pdo.


    -edit:
    Would this suffice? http://php.net/manual/en/mysqli.set-charset.php

    -edit: thought I paste the part from the manual i mentioned:
    PHP Code:
    /* change character set to utf8 */
    if (!$mysqli->set_charset("utf8")) {
        
    printf("Error loading character set utf8: %s\n"$mysqli->error);
    } else {
        
    printf("Current character set: %s\n"$mysqli->character_set_name());

    Last edited by aeternus; January 27th, 2013 at 04:36 PM.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by E-Oreo
    If I understand correctly, there are no security implications for UTF-8
    Well, I wouldn't necessarily say that.

    If MySQL uses latin1 as the default encoding (which it does if you haven't changed anything), "silently" switching to UTF-8 doesn't create a security risk. The backslash and quotes will still be recognized, and there's no risk of the escaping backslash being interpreted as a part of another multibyte character (which would make it lose its effect).

    However, if the default encoding is something like UCS-2 (the predecessor of UTF-16), then "silently" switching to UTF-8 would bypass the escaping completely (if I'm not mistaken). The escaping function would no longer recognize single quotes, because they're 0x0027 in UCS-2 but 0x27 in UTF-8. I couldn't get UCS-2 to work in MySQL, so I couldn't try it out. But this could really be a problem.

    Actually, if you rely on UTF-8, the good old addslashes() are somewhat more secure than a misconfigured PDO instance, because addslashes() works on ASCII. So you can be sure that all backslashes and quotes in the UTF-8 input will actually be escaped.

    So PDO is kind of like a double-edged sword: If you use it correctly, it's very secure. If you don't, it's even less secure than the most primitive escaping functions built into PHP.



    Originally Posted by aeternus
    Just wondering though, your recommendation to never use set names, is this also true for mysqli->prepare? I tend to use this instead of pdo.
    As far as I can tell, MySQLi always uses actual prepared statements, so even SET NAMES wouldn't necessarily break them -- but don't rely on it, especially since this will break mysqli->real_escape_string().

    mysqli->set_charset() is indeed the correct way of setting the character encoding of the connection, because it will also affect mysqli->real_escape_string().

    Comments on this post

    • aeternus agrees : Great thanks, This is what makes this forum awesome! cheers
  8. #5
  9. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Thanks for the heads-up!
    A tiny bit of topic, but related to the importance of character encoding for escaping functions, which I once read on a blog of Chris Shiflet: http://shiflett.org/blog/2005/dec/go...-vulnerability I thought I share it here

    Comments on this post

    • Jacques1 agrees

IMN logo majestic logo threadwatch logo seochat tools logo