www.webdeveloper.com
Results 1 to 14 of 14

Thread: How do I Prepared Statements for PHP/MySQL SQL Injection Prevention?

  1. #1
    Join Date
    Apr 2010
    Posts
    5

    How do I Prepared Statements for PHP/MySQL SQL Injection Prevention?

    I have a pages setup that seems to work fine, but I want to get some professional feedback as I am a bit of a newb at the prepared statement usage. I have a PHP page that uses several fields from a MySQL database table in various places. Can I place the prepared statement at the top of my page, then use variables within page, and then close at bottom of page? Here is my example...

    ===================================
    PHP Code:
    <?php require_once('includes/db.php'); ?>
    <?
        
    if (isset($_GET['pageTitle'])) {
            
    $pageTitle $_GET['pageTitle'];
        }
        else {
            
    $pageTitle 'work';
        }
        
    $result $db->prepare("SELECT `pageID`, `pageTitle`, `pageContent`, `metaTitle`, `metaDescription`, `metaKeywords` FROM `page` WHERE `pageTitle`=?");
        
    $result->bind_param("s"$pageTitle);
        
    $result->execute();
        
    $result->bind_result($pageID$pageTitle$pageContent$metaTitle$metaDescription$metaKeywords);
        
    $result->fetch();
    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title><?php echo $metaTitle?></title>
    <meta name="description" content="<?php echo $metaDescription?>" />
    <meta name="keywords" content="<?php echo $metaKeywords?>" />

    </head>

    <body>

    <div id="col1">
         <?php echo $pageContent?>

         <?php if ('contact' == $pageTitle) { ?>
              <p>blah, blah, blah</p>
         <? ?>
    </div>
    </body>
    </html>
    <?php
        
    // Free result set
        
    $result->close();

        
    // Close connection
        
    $db->close();
    ?>
    ===================================

    Any help is greatly appreciated.

    Bryan

  2. #2
    Join Date
    Apr 2010
    Posts
    5
    Title should be "How do I use Prepared Statements for PHP/MySQL SQL Injection Prevention?"

  3. #3
    Join Date
    Apr 2010
    Location
    Cocoa Beach, FL
    Posts
    41

    Protecting against SQL Injection with PHP - MySQL

    It is relatively easy to protect against a SQL Injection attack on a web application with a database. The best way is to ensure that all numeric type variables used in a Query are in fact numbers, before running the query. It is good that you are concerned about SQL injection as some serious and large scale thefts of credit card information have been commited using the SQL Injection attack on the internet.

    Usually a web page is vulnerable to SQL Injection if it has the following format, with a URL numeric variable is not properly validated to be a number, often URL variables are used in web applications. Like the following URL:

    http://www.somewebsite.com/RegisterUser.php?UserID=999

    The URL variable 999 is the primary key ID of a database table and is an integer type. This is often used in web applications on the internet, including web apps made with ASP .NET and ColdFusion. The problem occurs when a query like the following, in our example file RegisterUser.php, uses the UserID passed into the page as URL variable.

    $dbquery = "SELECT * FROM tblUser WHERE UserID = " . $_GET['UserID'];

    In this instance with the URL above, the query would evaluate to the following string:

    SELECT * FROM tblUser WHERE UserID = 999

    However, the risk for SQL Injection comes from the URL variable UserID, a malicious user could make the URL variable equal to an arbirtrary SQL command like the following:

    http://www.somewebsite.com/RegisterU...Table%tblUser;

    WHen this is evaluated by the PHP code, it will turn into the following string:

    SELECT * FROM tblUser WHERE UserID = 0;Drop Table tblUser;

    This is a valid SQL statement and will be executed by the database, as you can see, in this manner the maliscious user can add arbitrary SQL statements to a web page and have those statements executed by the DB. Even worse, if the DB query results are dynamically displayed on the webpage, the malicious user can also see all the results of the Querys they are adding to the page. In effect, the malicious user is granted total control of the database in this manner.

    This kind of attack was has been used by criminals to steal credit card numbers from web exposed Databases, especially DBs that are used in web based credit card transactions.

    The kind of SQL injection described herein usually only works with Numeric database column types, for string types there is not much risk because the data passed into the URL variable will be enclosed within quote marks within the SQL Query, so any SQL command inside the quotes will not be executed by the DB.

    The best way to protect against SQL injection is to make sure all numeric value variables that are to be used in a SQL Query are validated to make sure they are only numbers. There are many different ways to do this in PHP.

    Hope this helps, also SQL injection can be used in web forms where a numeric type in the form is not properly validated to ensure it is a number.

    Michael G. Workman
    michael.g.workman@gmail.com

  4. #4
    Join Date
    Apr 2010
    Posts
    5

    Prepared Statements, bind_result and echo variables

    Thank you for reply Michael. So how does this code look to you for secure GET retrieval of a string? Am I using it correctly to receive a single resultset? Also, the PHP page uses several fields from a MySQL database table in various places. Can I place the prepared statement at the top of my page (SEE BELOW), then use variables within page, and then close at bottom of page? Is there a better way?

    Code:
    <?php require_once('includes/db.php'); ?>
    <?
        if (isset($_GET['pageTitle'])) {
            $pageTitle = $_GET['pageTitle'];
        }
        else {
            $pageTitle = 'home';
        }
        //$pageTitle = 'home';
        if ($rsPage = $db->prepare("SELECT `pageID`, `pageTitle`, `pageContent`, `metaTitle`, `metaDescription`, `metaKeywords` FROM `page` WHERE `pageTitle`=?")) {
        $rsPage->bind_param("s", $pageTitle);
        $rsPage->execute();
        $rsPage->bind_result($pageID, $pageTitle, $pageContent, $metaTitle, $metaDescription, $metaKeywords);
        $rsPage->fetch();
        }
    ?>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title><?php echo $metaTitle; ?></title>
    <meta name="description" content="<?php echo $metaDescription; ?>" />
    <meta name="keywords" content="<?php echo $metaKeywords; ?>" />
    
    </head>
    
    <body>
    
    <div id="col1">
         <?php echo $pageContent; ?>
    
         <?php if ('contact' == $pageTitle) { ?>
              <p>blah, blah, blah</p>
         <? } ?>
    </div>
    </body>
    </html>
    <?php
        // Free result set
        $result->close();
    
        // Close connection
        $db->close();
    ?>

  5. #5
    Join Date
    Apr 2010
    Posts
    5
    Can I close the prepared statement and still use bind_result variables throughout the page like this? Is this a better method than closing on bottom of page?

    Code:
    <?php require_once('includes/db.php'); ?>
    <?
        if (isset($_GET['pageTitle'])) {
            $pageTitle = $_GET['pageTitle'];
        }
        else {
            $pageTitle = 'home';
        }
        //$pageTitle = 'home';
        if ($rsPage = $db->prepare("SELECT `pageID`, `pageTitle`, `pageContent`, `metaTitle`, `metaDescription`, `metaKeywords` FROM `page` WHERE `pageTitle`=?")) {
        $rsPage->bind_param("s", $pageTitle);
        $rsPage->execute();
        $rsPage->bind_result($pageID, $pageTitle, $pageContent, $metaTitle, $metaDescription, $metaKeywords);
        $rsPage->fetch();
        }
        // Free result set
        $rsPage->close();
    ?>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title><?php echo $metaTitle; ?></title>
    <meta name="description" content="<?php echo $metaDescription; ?>" />
    <meta name="keywords" content="<?php echo $metaKeywords; ?>" />
    
    </head>
    
    <body>
    
    <div id="col1">
         <?php echo $pageContent; ?>
    
         <?php if ('contact' == $pageTitle) { ?>
              <p>blah, blah, blah</p>
         <? } ?>
    </div>
    </body>
    </html>
    <?php
        // Close connection
        $db->close();
    ?>

  6. #6
    Join Date
    Mar 2010
    Posts
    672
    Quote Originally Posted by Michael_G View Post
    The kind of SQL injection described herein usually only works with Numeric database column types, for string types there is not much risk because the data passed into the URL variable will be enclosed within quote marks within the SQL Query, so any SQL command inside the quotes will not be executed by the DB.
    Sorry but that's not true. If you do no validation on the string, the user can still insert their own ' or " and end the string, thus allowing anything following that quote to be counted as a sql command. Even if you filter out quotes from the url var there are still ways to get encoded forms through. However, with that said, you should also be using quotes around numeric variables in a sql command, its perfectly valid.
    For true protection from both xss and sql injection attacks you shoudl combine escaping with some sort of whitelist/blacklist filtering system. Escaping alone can still be compromised in certain situations.
    Last edited by Jarrod1937; 04-19-2010 at 03:27 PM.

  7. #7
    Join Date
    Apr 2010
    Posts
    5

    mysqli_real_escape_string with prepared statement

    Would this code be even more safe as it would escape the $_GET variable before the prepare statement is even declared?

    Code:
    <?
        require_once('includes/db.php');
    
        if (isset($_GET['pageTitle'])) {
            $pageTitle = mysqli_real_escape_string($db, trim($_GET['pageTitle']));
        }
        else {
            $pageTitle = 'home';
        }
        //$pageTitle = 'home';
        if ($rsPage = $db->prepare("SELECT `pageID`, `pageTitle`, `pageContent`, `metaTitle`, `metaDescription`, `metaKeywords` FROM `page` WHERE `pageTitle`=?")) {
        $rsPage->bind_param("s", $pageTitle);
        $rsPage->execute();
        $rsPage->bind_result($pageID, $pageTitle, $pageContent, $metaTitle, $metaDescription, $metaKeywords);
        $rsPage->fetch();
        // Close statement
        $rsPage->close();
        }
    
        // Close database connection
        $db->close();
    ?>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title><?php echo $metaTitle; ?></title>
    <meta name="description" content="<?php echo $metaDescription; ?>" />
    <meta name="keywords" content="<?php echo $metaKeywords; ?>" />
    
    ....rest of code

  8. #8
    Join Date
    Mar 2010
    Posts
    672
    I could be wrong but i think prepare does the same type of escaping as the mysqli_real_escape_string function. If so, then it will probably still only offer the same amount of security as before.
    Although i should be clear, in my previous post i was replying specifically to michael's post. Technically escaping is better than nothing, and is sufficient in most cases, so don't worry too much about what i said earlier. However, there are ways around escaped strings, but it is up to you if you want to be paranoid enough to worry about it. Though, i personally would worry about it and make sure you follow the best practices. Here is a good quickie on how mysql's escape functions can fail, sometimes through incorrect usage like not using quotes around numerical results, like mentioned in my previous post.
    http://www.webappsec.org/projects/articles/091007.shtml
    Last edited by Jarrod1937; 04-19-2010 at 04:47 PM.

  9. #9
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,388
    If you use prepared statements with the correct place-holder type for each external value, then you should be OK, as numeric type place-holder values will essentially be cast to that type (so "1; drop table users;--" will be converted to "1") and string types will be escaped and quoted (so the preceding example would become "'1\; drop table users\;--;", which may not be particularly desirable but will not execute the attempted injection).

    You do not want to both escape a value and then use it in a prepared statement place-holder value, as it will be doubly escaped, and you'll end up with unwanted back-slashes stored in the DB. (The above example would then become "'1\\\; drop table users\\\;--'", which would be stored in the DB as "1\; drop table users\;--".)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  10. #10
    Join Date
    Sep 2008
    Posts
    408
    With prepared statements you shouldn't have any issues. As long as the prepared statement itself doesn't have any user input.

    PHP Code:
    <?php
    $value 
    'something';
    /* BAD!!!! */ $sql $db->prepare("SELECT this_is_bad WHERE type='".$_GET['type']."' AND value=?");
    $db->bind_param('s',$value);
    $db->execute();
    ?>
    However if all user values are sent via bind_param then it will be cleaned for you. In essence you're sending to MySQL a statement to be prepared, all commands sent after that are just the values being sent, not the entire query.

    Also, bind_params is a reference, not a value, so changing the value of $value and executing again will change the results.
    Mullanaphy!
    http://www.mullanaphy.com/

    Unless code is provided or an exact example is requested I think I'm going to start using psuedo code from now on...

    Also, I freelance as well. Inquire within!

  11. #11
    Join Date
    Jan 2009
    Posts
    3,346
    Quote Originally Posted by Shorts View Post
    With prepared statements you shouldn't have any issues. As long as the prepared statement itself doesn't have any user input.

    PHP Code:
    <?php
    $value 
    'something';
    /* BAD!!!! */ $sql $db->prepare("SELECT this_is_bad WHERE type='".$_GET['type']."' AND value=?");
    $db->bind_param('s',$value);
    $db->execute();
    ?>
    However if all user values are sent via bind_param then it will be cleaned for you. In essence you're sending to MySQL a statement to be prepared, all commands sent after that are just the values being sent, not the entire query.

    Also, bind_params is a reference, not a value, so changing the value of $value and executing again will change the results.
    FYI- Your example could have user input: $_GET['type'].

  12. #12
    Join Date
    Sep 2008
    Posts
    408
    Quote Originally Posted by criterion9 View Post
    FYI- Your example could have user input: $_GET['type'].
    Indeed, that is why I put the /* BAD!!! */ in front. To show what not to do with a prepared statement.
    Mullanaphy!
    http://www.mullanaphy.com/

    Unless code is provided or an exact example is requested I think I'm going to start using psuedo code from now on...

    Also, I freelance as well. Inquire within!

  13. #13
    Join Date
    Jan 2009
    Posts
    3,346
    Sorry, I missed that part somehow. lol.

  14. #14
    Join Date
    Sep 2008
    Posts
    408
    Heh, it's all good. I meant to provide a "good" version of the same code yet was side tracked (which happens often).
    Mullanaphy!
    http://www.mullanaphy.com/

    Unless code is provided or an exact example is requested I think I'm going to start using psuedo code from now on...

    Also, I freelance as well. Inquire within!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles