SQL Injection in server

We had a security audit on our code, and they mentioned that our code is vulnerable to SQL Injection attack.

Explanation- Below code ($rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());) of server_processing.php invokes a SQL query built using input coming from an untrusted source. This call could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.

/*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";
    **$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());**

SQL injection errors occur when: 1. Data enters a program from an untrusted source. 2. The data is used to dynamically construct a SQL query.

Example : The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user. ...

$userName = $_SESSION['userName'];
$itemName = $_POST['itemName'];
$query = "SELECT * FROM items WHERE owner = '$userName' AND itemname = '$itemName';";
$result = mysql_query($query);

... The query that this code intends to execute follows:

SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;

However, because the query is constructed dynamically by concatenating a constant query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:

SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';

The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:

SELECT * FROM items;

This simplification of the query allows the attacker to bypass the requirement that the query only return items owned by the authenticated user; the query now returns all entries stored in the items table, regardless of their specified owner.

SO we have sql injection in below code-

$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
    $sOrder = "";
}

5- Individual column filtering -

    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            **$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";**
        }
    }

Please help how can I remove sql injection in my code.

Can anyone help where I am missing things? Anyone help is appreciated !

You can just replace each single quote with a double single quote.

$itemName = str_replace("'","''",$name);

This will work in a pinch if you aren't able to make any significant changes. Though the previous comment is correct. The best method would be using parameterized queries.

上一篇: 本地html文件中的图像没有在IE中显示

下一篇: 服务器中的SQL注入