PHP detect SQL injection attempt

Question

My code is already safe, using parameters in SQL queries, but, I would like to detect if anyone attempts to inject something into a submit form.

I found Snort, but I would need something that would be at PHP script level, not the whole network.

This is for a site that contains personal information for students and thus, we will warn (or even take action against) anyone that even tries an attack.

"little" bit overkill using phpids. All SQL injections start with a single/double qoute and some SQL keywords to test if there is a injection on POST or GET requests. — Raymond Nijland, Apr 28 '18 at 13:42
I tried phpids and eventually installed enygma/expose. I wonder, is there something similar and the nginx level? I could reuse the filters used by ee. — Michael Chourdakis, Apr 29 '18 at 11:44
@RaymondNijland _“All SQL injections start with a single/double qoute”_ - no, they don’t. Please don’t state stuff like this, would be dangerous if someone actually believed it after reading it here. — misorude, Dec 17 '18 at 13:37
i meant to say all SQL injection attacks **starts testing** with single/double qoutes @misorude if they work they add other keywords.. i think you understand it wrong and or i should write it down more clearly — Raymond Nijland, Dec 17 '18 at 13:43
@RaymondNijland that everyone trying to test this would start by using a value containing quotes is more something you assume, not necessarily what people will actually do. And it doesn’t necessarily _require_ quotes, it depends on the specific queries to begin with. (My point was mainly the latter, just wanted to make sure no one misunderstood what you said as “if it doesn’t contain any quote characters, can’t be dangerous.”) — misorude, Dec 17 '18 at 14:22

BillyK. · Answer 1 · 2018-12-18T23:06:31.080

I have created a very basic and simple PHP class for checking / detecting SQL injection attempts.

<?php
/**
 * simpleSQLinjectionDetect Class
 * @link      https://github.com/bs4creations/simpleSQLinjectionDetect 
 * @version   1.1
 */

class simpleSQLinjectionDetect
{   
    protected $_method  = array();
    protected $_suspect = null; 

    public $_options = array(
                            'log'    => true,
                            'unset'  => true,
                            'exit'   => true,
                            'errMsg' => 'Not allowed',
                        );

    public function detect()
    {
        self::setMethod();

        if(!empty($this->_method))
        {
            $result = self::parseQuery();

            if ($result)
            {
                if ($this->_options['log']) {
                    self::logQuery();
                }

                if ($this->_options['unset']){
                    unset($_GET, $_POST);
                }

                if ($this->_options['exit']){
                    exit($this->_options['errMsg']);
                }
            }
        }
    }

    private function setMethod()
    {
        if ($_SERVER['REQUEST_METHOD'] === 'GET') {
            $this->_method = $_GET;
        }

        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $this->_method = $_POST;
        }
    }

    private function parseQuery()
    {
        $operators = array(
            'select * ',
            'select ',
            'union all ',
            'union ',
            ' all ',
            ' where ',
            ' and 1 ',
            ' and ',
            ' or ',
            ' 1=1 ',
            ' 2=2 ',
            ' -- ',
        );

        foreach($this->_method as $key => $val)
        {
            $k = urldecode(strtolower($key));
            $v = urldecode(strtolower($val));

            foreach($operators as $operator)
            {
                if (preg_match("/".$operator."/i", $k)) {
                    $this->_suspect = "operator: '".$operator."', key: '".$k."'";
                    return true;
                }
                if (preg_match("/".$operator."/i", $v)) {
                    $this->_suspect = "operator: '".$operator."', val: '".$v."'";
                    return true;
                }
            }
        }
    }

    private function logQuery()
    {
        $data  = date('d-m-Y H:i:s') . ' - ';
        $data .= $_SERVER['REMOTE_ADDR'] . ' - ';
        $data .= 'Suspect: ['.$this->_suspect.'] ';
        $data .= json_encode($_SERVER);
        @file_put_contents('./logs/sql.injection.txt', $data . PHP_EOL, FILE_APPEND);
    }
}

/* then call it in your app...
*********************************************/
$inj = new simpleSQLinjectionDetect();
$inj->detect();

You can check it on github also

This is a very simple and basic class. Any suggestions for improvements / updates are welcome :)

`SELECT * FROM account WHERE username = whatever AND password = '' OR 2=2 -- '` — hanshenrik, Dec 17 '18 at 18:17

Martin · Answer 2 · 2018-12-17T19:23:02.133

This is actually quite a hard topic. BillyK may have a semi-viable approach but it's better to let MySQL do the hard work for you; therefore:

1) Run the user-constructed (ie unsafe) query in a MySQL Transaction.
2) How many results does it give? (Check for both rows returned and rows affected)
3) Record any MySQL error warning logs.
4) Cancel / rollback the Transaction. So that nothing has changed on your database.
5) Re-run the query with the paramaterised variable (ie safe)
6) How many results does it give? (Check for both rows returned and rows affected)
7) Check if (6) gives a different number of results to (2) or if (5) gives any SQL error warnings. You can also use PHP array comparison features to check if the result sets are equal.
8) Any positives come up, such as differences in result counts, result set eqauality or SQL warnings, then record that query string into a save file for human review.

Concept Thoughts:

With a properly implemented system of Prepared Statements it is not possible for SQL injection to occur from user variables as data strings. Therefore, rather like people throwing water balloons at tanks; it's also pretty worthless to try and "detect" these infractions; they in themselves don't show you anything more than someone read some website that offers such methods.

Therefore, as long as you have built your PHP/SQL correctly then any number or any quality of SQL injecton attempts are just water off a ducks back, and you cumulatively waste more processing power and time and effort trying to detect and record them than you would simply to ignore them.

Please note that COMMIT can be done not only as a php method call, but also as a SQL statement, so an attacker could easily commit changes with this approach. If user privileges are defined in the database, the attacker can easily gain admin privileges. Althoug the concept is right, this approach should be adopted only on a real sandbox VM, with all the due cares. — Daniels118, Jan 15 '22 at 11:14

PHP detect SQL injection attempt

2 Answers2

Concept Thoughts:

Linked

Related