Announcement Announcement Module
No announcement yet.
Mysql Injection and how can it be prevented Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql Injection and how can it be prevented

    SQL Injection is a type of web application security vulnerability in which an attacker is able to submit a database SQL command which is executed by a web application, exposing the back-end database.Attackers provide specially crafted input data to the SQL interpreter and trick the SQL interpreter to execute unintended commands.SQL Injection allows an attacker to create, read, update, alter, or delete data stored in the back-end database.A SQL Injection attack can occur when a web application utilizes user-supplied data without proper validation or encoding as part of a command or query.

    SQL Injection Example

    // Normal input
    $name = "any name";
    $query = "SELECT * FROM customers WHERE username = '$name'";

    // SQL Injection input
    $name_bad = "' OR 1'";
    $query = "SELECT * FROM customers WHERE username = '$name_bad'";

    Normal: SELECT * FROM customers WHERE username = 'any name'
    Injection: SELECT * FROM customers WHERE username = '' OR 1''

    Normal query will display all the records from Customers table with name= 'any name'
    But,the injection input behave differently. By using a single quote (') the string part of the query would be ended. This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!
    SQL injection would be more serious if the hacker uses DDL or DML commands in injection.

    $name_bad = "'; DELETE FROM customers WHERE 1 or username = '";

    SELECT * FROM customers WHERE username = ''; DELETE FROM customers WHERE 1 or username = '';

    The above query will delete all the records from customers table.

    Tips to Prevent SQL Injection Attacks

    Option 1: Prepared Statements

    Instead of of passing the variable($name_bad in our example) directly to the SQL query, we bind a PHP variable to a corresponding named placeholder in the SQL statement .

    PDO:: prepare() - Prepares a statement for execution and returns a statement object

    //Create a new PDO object ($DB) by passing all the DB connection details
    $query = "SELECT * FROM customers WHERE username = ':name_bad'";

    PDOStatement::bindValue() - Binds a value to a parameter


    PDOStatement::execute() - Executes a prepared statement


    Option 2: Stored Procedures

    Using dynamic statements in a stored procedure makes it vulenrable to SQL injection. If it can not be avoided, the easiest way is to use parameterization and sp_executesql instead of EXEC or EXECUTE to execute the dynamically generated statement in order to be safe. Parameterization allows to specify the datatype and the generated SQL statement will reference parameters as variables. Hence it will not use the user defined input to generate the statement.

    CREATE PROCEDURE sp_Customer(@Name NVARCHAR(50))
    SET @sqlcmd = N'SELECT * FROM customers WHERE username = '$name';
    SET @params = N'@Name NVARCHAR(50)';
    EXECUTE sp_executesql @sqlcmd, @params, @Name;

    Option 3: Escaping All User Supplied Input

    PHP has a specially-made function mysql_real_escape_string() to prevent these attacks.
    What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.
    Our above example would be rewrited as
    $name_bad = "' OR 1'";
    $name_bad = mysql_real_escape_string($name_bad);
    now, the output would be :
    SELECT * FROM customers WHERE username = '\' OR 1\''

    Now the above query will just try to find a record with name = \' OR 1\'