Possible SQL Injection in AJAX request?

Question

I am developing search indexing using PHP and AJAX to make it powerful. When I scan it using burpsuit or other security scanner, SQL injection appears in AJAX code and I can't find any solution for it. The code is below:

<?php

require_once 'Connections/connect.php';

if($_GET['type'] == 'mobile'){
    $result = mysql_query("SELECT mobilep FROM dictionary where mobilep LIKE '".$_GET['name_startsWith']."%'"); 
    $data = array();
    while ($row = mysql_fetch_array($result)) {
        array_push($data, $row['mobilep']); 
    }   
    echo json_encode($data);
}


?>

The solution is to switch from the MySQL extension to MySQLi or PDO, and to use bind variables..... or at the very least to ensure that user input is escaped before injecting it into SQL queries — Mark Baker, Mar 21 '15 at 17:42
Start using prepared statement with bind variables or if you need some time to learn it use `mysql_real_escape_string()` before using the requested data into the query as `where mobilep LIKE '".mysql_real_escape_string($_GET['name_startsWith'])."%'"` http://php.net/manual/en/function.mysql-real-escape-string.php — Abhik Chakraborty, Mar 21 '15 at 17:42
See this question for your answer http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Tyler Replogle, Mar 21 '15 at 17:44

score 0 · Answer 1 · answered Mar 21 '15 at 17:45

This is very bad... you're using the deprecated mysql adapter.

http://php.net/manual/en/book.pdo.php

Use pdo and binds, here's a full prototype:

class MySql
{
    private $sDbName      = '';
    private $sUsername    = '';
    private $sPassword    = '';
    private $sHost        = '';
    private $oConnection  = null;
    public function __construct()
    {
        $this->oConnection = new PDO( 
            'mysql:host=' 
            . $this->sHost 
            . ';dbname=' 
            . $this->sDbName, 
            $this->sUsername, 
            $this->sPassword 
            );
    }
    public function getDb()
    {
        return $this->oConnection;
    }
}
$aReturn[ 'data' ] = '';
if( !empty( $_GET[ 'type' ] )
    && ( !empty( $_GET[ 'name_startsWith' ] ) 
    && ( $_GET['type'] == 'mobile' ) 
    )
{
    $oMySql = new MySql;
    $oDb = $oMySql->getDb();
    $sSql = "SELECT mobilep FROM dictionary where mobilep LIKE :name";
    $aBinds[ ':name' ] = $_GET[ 'name_startsWith' ] . '%';

    $oStmp = $oDb->prepare( $sSql );
    $oMySql->bindVariables( $oStmp, $aBinds );
    $oStmp->execute();
    $oResults = $oStmp->fetchall();
    if( !empty( $oResults ) )
    {
        // var_dump( $aResults );
        $oErrors = $oStmp->errorInfo();
        // var_dump( $oErrors );
        $aReturn[ 'data' ] = $aResults;
    }
}
$sJson = json_encode( $aReturn, 1 );
header( 'Content-type', 'application/json' );
echo $sJson;

Essentially replacing placeholders with data and elimination potential sql injection because PDO adapter will take care of any incorrect values/invalid queries. http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection — Vladimir Ramik, Mar 21 '15 at 18:46
i tried your code but couldn't work for my script i am using this ajax code for autocomplete functions .. do you have anysolution can you offer without using PDO — user3167044, Mar 21 '15 at 18:51

score 0 · Answer 2 · answered Apr 14 '16 at 20:22

(Yes, this is question over a year old. But there is no selected answer. I ran across this question in a search...)

If you are stuck with mysql_ interface functions, and can't migrate to mysqli or PDO, the best you can do is to use the mysql_real_escape_string function.

existing code:

= mysql_query(" ... LIKE '". $_GET['name_startsWith'] ."%'");

to properly escape a potentially unsafe value, before it's incorporated into the SQL text, use the mysql_real_escape_string function...

= mysql_query(" ... LIKE '". mysql_real_escape_string( $_GET['name_startsWith'] )."%'");
                             ^^^^^^^^^^^^^^^^^^^^^^^^^                          ^

Possible SQL Injection in AJAX request?

2 Answers2