Announcement Announcement Module
Collapse
No announcement yet.
SQL Injection by Prepared Statements Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Injection by Prepared Statements

    SQL injection is not particularly a big issue if you are using prepared statement for all your SQL queries.

    So what are prepared statements? Prepared statements actually seperate SQL logic and data. First, when you prepare a query, MYSQL will parse the query string and set aside placeholders for the question marks in the query.

    Then when data is bound, MYSQL will execute the query with the data bound to the question marks. You can actually prepare one query string and execute it multiple times with different data with no additional overhead, very very useful.

    Prepared statements are also faster if you're running the same query often, since your database won't need to interpret the command every time it's sent.

    Look at the SQL statement in the example below. You can see that instead of putting the $title we want directly into the query, we put a placeholder instead, called :title, and then we "bind" the value we want to the placeholder. PDO then passes the SQL and the data to the database separately.

    Code:
    $query = 'SELECT * FROM my_table WHERE title = :title';
    
    $stmt = $db->prepare($query);
    $stmt->bindValue(':title', $title);
    
    Here's a full example:
    
    <?php
    $db = new PDO('mysql:host=hostname;dbname=defaultDbName',
    'username', 'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    
    $query = 'SELECT * FROM my_table WHERE title = :title';
    
    $stmt = $db->prepare($query);
    $stmt->bindValue(':title', $myTitle);
    $stmt->execute();
    
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // ...
    }
    ?>
    In the above example, I've used bindValue(). You should know the difference is between the PDO functions bindValue() and bindParam(). bindParam() connects the variable by reference instead of just taking the value from the variable. This means if you change the variable, you change the SQL statement. Its pretty useful when we want to execute multiple queries of the same type while varying parameter values.
    Prepared statements can save you from all types of SQL injections. Still there are some common problems:

    First problem is that you can't bind multiple parameters in prepared statements, because PDO doesn't support arrays in prepared statements, for example:

    Code:
    SELECT * FROM my_table WHERE id IN (1, 5, 7);
    To do this in PDO, you'll have to escape the values manually using the quote() method. Here's an example:

    Code:
    <?php
    $db = new PDO('mysql:host=hostname;dbname=defaultDbName',
    'username', 'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    
    $names = array('Haris', 'Josh', 'Jeff');
    $values = array_map(array($db,'quote'),$names);
    $query = 'SELECT * FROM my_table WHERE name IN ('.join(',',$values).')';
    
    $result = $db->query($query);
    
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // ...
    }
    ?>
    The other problem is that Prepared statement cannot be used for table names, column names, and other keywords.

    Have a look on how Yii Framework solved the above 2 problems.

    When using Active Record in Yii, the first problem can be solved by using the criteria addInCondition method as follows:

    Code:
    $criteria=new CDbCriteria;
    $criteria->addInCondition('categoryid',$this->categoryid,true);
    $criteria->addInCondition('id',array(1,2,3,4,5,6));
    The second problem can also be solved by Active record and PDO in Yii,

    Code:
    $Criteria = new CDbCriteria();
    $Criteria->condition = "`t`.`title` = $_GET['title']";
    $records = MyActiveRecord::model()->findAll($Criteria);
    Happy coding.

    Thank you.
Working...
X