Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    1

    Prepared Statements Versus SQL injections


    Will using prepared statements alone protect me against SQL injections?

    What other techniques must I employ to avoid problems with SQL injections?
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,692
    Rep Power
    6351
    As long as you do it properly, prepared statements will protect you from SQL injection attacks, but not any other kind of injection attack, xsf, xss, etc.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    1

    Re:


    Originally Posted by ManiacDan
    As long as you do it properly, prepared statements will protect you from SQL injection attacks, but not any other kind of injection attack, xsf, xss, etc.
    I've managed to convert my PHP code into PDO w/ prepared statements to avoid SQL attacks. My statements always get blasted for having security issues, and I'm wondering if the following snippet of my code also has SQL vulnerabilities

    PHP Code:
    $stmt $con->prepare("INSERT INTO pixs(title, Price, location, Description, picname, picname2, date, phonenumber, email) VALUES(:title, :Price, :location, :Description, :picname, :picname2, :date, :phonenumber, :email)");
    $stmt->execute(array(':title' => $title':Price' => $price':location' => $location':Description' => $description':picname' => $picname':picname2' => $picname2':date' => $today':phonenumber' => $phonenumber':email' => $email)); 
    Thanks friends
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,922
    Rep Power
    1045
    Hi,

    unfortunately, PDO has an evil gotcha: By default, it uses "pseudo-statements" rather than actual prepared statements.

    With real prepared statements, you have two separate steps: First, you send a query template to the database system. This template gets parsed, optimized etc. And then you send the input data and have the statement executed. Since the input is separated from the actual query, it cannot be used to inject SQL commands or manipulate the original query. SQL injections cannot occur.

    However, the "pseudo-statements" used by PDO only look like you have two steps. In fact, PDO takes the input, escapes it, inserts it into the query template and then sends the whole thing to the database. So it's a normal query with escaped values. This usually works, but it's not guaranteed to prevent SQL injections. If you get the character encoding wrong, you may very well get attacked (see the link above).

    So if you're using PDO, proper configuration is crucial. You must turn the "pseudo-statements" off:

    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 
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,868
    Rep Power
    368
    i think it is odd that PDO has chosen to emulate prepared statements as default.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,922
    Rep Power
    1045
    Funny thing is, it's not even possible to get this emulated stuff secure prior to PHP 5.3.6, because the PHP developers somehow forgot to add proper character encoding support.

    This is like removing the chemical from an fire extinguisher in order to replace it with plain water. Pretty dangerous, because people don't expect it and might use it wrong. But, well, it should work most of the time. And then you forget to fill in the water.

    But did you really expect intelligent decisions from the inventors of "register globals" and "magic quotes"? Sometimes I'm surprised this language doesn't just blow up into a big cloud of garbage.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,660
    Rep Power
    4123
    I had been lead to believe that MySQL could not support prepared statements, which is why it was emulated in PDO..and implemented proper for other RDBMS vendors.

    Only recently did I find out that this is not the case

    MySQL 5.0 (2004) and later supports prepared statement

    MySQL 5.0 offered the mysqli c library which, I believe, is what lies behind the mysqli PHP extension....and it has certainly been in place since php 5.0 (2004).

    So for 9 years we have had prepared statements available in MySQL and implementable by PHP.

    Here's the terrible thing that affected me - and many others. When I started writing PHP (in 2004, coincidently) all the tutorials referenced the (now deprecated) mysql extension.

    The manual hinted that there was an "improved" library but, at the time, did not specify a reason to use it (eg why it was better), other than it had object support (something I couldn't get my head round at the time).

    Once I had learned how to connect to a database and use it I felt that there was no reason to go an re-learn what I already knew...why would I when I'm not altered to new methods or technologies?

    So the beauty of working in a team, or contributing to communities like this is that we become aware of new features, improvements, etc. It's then our responsibility to investigate further - why is it better? How does it benefit me? how can I learn more?

    So, If you're a hobbyist, or work in a team of one then take on board what you learn and find out about. It can certainly seem daunting at the time - I know I feel that I can't keep up with technology and that I've been overtaken.....but i've learned not to let it bother me and to focus on concepts that are perhaps more important: As a web / web app developer I need to make sure my products are well designed, meet client specifications, are secure and scale well where necessary. I can do all this without being at the cutting edge. Cutting edge stuff may be exciting but can you implement it in your current commercial offering?. Next year it'll have proved its self, will be well documented and will have a community supporting it. I'm certainly no giant, but am certainly welcome to sit on their shoulders
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,805
    Rep Power
    529
    Yikes! What do you mean PDOs might not protect against injection?

    I am running PHP Version 5.3.18 and MySQL 5.5.28. I only access the database using one of the four below methods, and rarely only use the last method as I am certain of each columns type when I create the query. I am honestly a little fuzzy on all that charset and utf8 stuff, and probably need to learn up on it.

    What should I be doing differently? Thank you

    PHP Code:
    class db {
        private static 
    $instance NULL;
        private function 
    __construct() {}   //Make private
        
    private function __clone(){}   //Make private
        
    public static function db() //Get instance of DB
        
    {
            if (!
    self::$instance)
            {
                
    self::$instance = new PDO("mysql:host=localhost;dbname=myDBName"'myUserName''myPassword');
                
    self::$instance->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
            }
            return 
    self::$instance;
        }
        public static function 
    script($file//Get instance of script
    }

    $sql ='SELECT column1 FROM myTable WHERE id='.(int)$id;
    $stmt db::db()->query($sql);

    $sql ='SELECT column1 FROM myTable WHERE id=? and column2=?';
    $stmt db::db()->query($sql);
    $stmt->execute(array($id,'myString'));

    $sql ='SELECT column1 FROM myTable WHERE id=:id and column2=:column2';
    $stmt db::db()->query($sql);
    $stmt->execute(array('id'=>$id,'column2'=>'myString'));

    $sql ='SELECT column1 FROM myTable WHERE id=:id and column2=:column2';
    $stmt db::db()->query($sql);
    $stmt->bindParam(':id'$idPDO::PARAM_INT);
    $stmt->bindParam(':column2''myString'PDO::PARAM_STR12);
    $stmt->execute(); 
  16. #9
  17. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,660
    Rep Power
    4123
    The crux is the emulation of prepared statements

    By default, PDO emulates prepared statements - the interface (ie the code you write and functions you use) is identical but PHP is doing the 'preparing' instead of mysql.

    However, all you have to do is to add an array of options to your PDO call to get round this.

    In your code:

    PHP Code:
    self::$instance = new PDO("mysql:host=localhost;dbname=myDBName"'myUserName''myPassword'); 
    becomes

    PHP Code:
    self::$instance = new PDO("mysql:host=localhost;dbname=myDBName"'myUserName''myPassword',array(PDO::ATTR_EMULATE_PREPARES => false)); 
    see Jacques1 post (reply #4) for full details
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    1

    Re:


    Dynamite advice my friends.

    I now have my application working where I used prepared statements to connect to my DB, search my page live, or send info to my DB.

    Now what I'm wondering is if I collect information from a form and then use $_POST to display it on the next page... do I still need to protect against SQL attacts?

    And do you still need to escape variables before plugging them into prepared statements?

    I tried typing statements live <div> and <script> in my form, but my browser blocks them automatically

    I appreciate everybody's ideas. Let's keep this convo going.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,805
    Rep Power
    529
    Thanks Northie!

    Looks like I can set these either by passing the optional array to PDO(), or using setAttribute() as I originally did. Probably best to do so by passing the array, agree?

    Also, your example did not set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC as Jacques1's did Sounds like this is a personal choice.

    Lastly, I probably need/want to add charset=utf8 to the first parameter passed to PDO(), correct? Does this just encode all characters in such a way that they cannot inadvertently inject SQL?

    Thank you
  22. #12
  23. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,660
    Rep Power
    4123
    Originally Posted by phpnewbie34
    Dynamite advice my friends.

    I now have my application working where I used prepared statements to connect to my DB, search my page live, or send info to my DB.

    Now what I'm wondering is if I collect information from a form and then use $_POST to display it on the next page... do I still need to protect against SQL attacts?

    And do you still need to escape variables before plugging them into prepared statements?

    I tried typing statements live <div> and <script> in my form, but my browser blocks them automatically

    I appreciate everybody's ideas. Let's keep this convo going.
    you should 'escape' the data for the medium into which you're injecting it.

    When you send data to mysql use prepared statements (no need to 'escape' prior, the preparing and executing sorts all this out for you behind the scenes).

    When you display content in html you should be using htmlentities.

    If you've stored user data in a database and then want to display it on a page then use htmlentities after the select, before the echo (don't store the escaped html - you may want to use it in a non-html context another time). In this case it may not be necessary to use htmlentites all the time as your database field types will offer some protection (eg ints, floats and enums which you control).

    Originally Posted by phpnewbie34
    I tried typing statements live <div> and <script> in my form, but my browser blocks them automatically
    Did it? really? What about someone else's browser? have done a 'view source' in the final page and seen that they're not there?

    Allowing raw html tags opens you up to XSS (cross site scripting). I could make a post request to your page and put in a script tag which linked to a malicious script. Closely related is XSRF (cross site request forgery)
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  24. #13
  25. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,660
    Rep Power
    4123
    Originally Posted by NotionCommotion
    Thanks Northie!

    Looks like I can set these either by passing the optional array to PDO(), or using setAttribute() as I originally did. Probably best to do so by passing the array, agree?

    Also, your example did not set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC as Jacques1's did Sounds like this is a personal choice.

    Lastly, I probably need/want to add charset=utf8 to the first parameter passed to PDO(), correct? Does this just encode all characters in such a way that they cannot inadvertently inject SQL?

    Thank you
    Right,

    I create an array of options and send this as the fourth parameter to PDO

    I use FETCH_ASSOC, but my legacy code has it somewhere else.

    charsets:

    The easiest and least buggy thing to do is to get everything talking in utf8. So in your database set the charset to utf8 and the collation to utf8 general or utf8 unicode. The swedish thing is in there by default because the guy who first developed mysql was swedish (and had a daughter called My).

    Then, connect to the database and specify utf8

    Then, in your html set the charset to uft8

    Then, save your files as utf8

    Specifying utf8 in the pdo call:

    versions of php prior to PHP 5.3.6 did not allow the charset attribute in the DSN, so set names had to be used. Set names can be buggy. try to upgrade to 5.3.6 or later (ideally current version of php5.4)

    I'm sure Jacques1 will be on my case if I've missed anything
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,805
    Rep Power
    529
    Thanks again Northie,

    I still have some questions about charsets. Instead of continuing these questions on this post about prepared statements and SQL injection, I will start a new post.

    In regards to injection and PDO, you think you get it and then find there is something else critical that you need to do (i.e. PDO::ATTR_EMULATE_PREPARES => false). I started putting together a checklist of things I need to remember to do, and it just keeps on getting longer!
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    1

    Re:


    I tried using html entities to escape HTML tags ... but it's not working.

    If somebody enters <script> or <div>, how do I remove the '<>'s?

    How do I use htmlentities with PDO functions, not mysqli?
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo