Panacea for SQL Injection - Queries with Parameters

Introduction

This topic is the beginning of a short cycle about panacea for various vulnerabilities in Web applications.

It just so happened that a large number of Web applications suffer from SQL injection. And how many do not write articles, how bug reports are not published - all the same, programmers clog forget that the user can be substituted into the parameters of their application to all that they want.

I suggest they, and indeed everyone use the so-called prepared-statements . They are also called parameterized queries .
The idea is simple, like all ingenious - to separate the request from user data.
You probably ask: what does it mean to "separate" ? This refers to: separate them from the compilation of the request.
After all, your site should process user data, and not make SQL queries based on them (although who knows?). The latter even sounds silly, but it is precisely because of this that we have a similar situation.

Implementation

Implementation is no more complicated than an idea. For example, let's take a request to get a blog entry: In PHP code, it will look something like this: The security problem is visible to the naked eye - the url_title parameter of the address bar is not filtered. This fact is usually revealed when one of the visitors has already found it. B then the code will be converted to a more terrible look: Some queries look especially terrible: I even made a mistake when I printed this safe example and made a syntax error somewhere around quotation marks and escape slashes. The larger the request, the greater the confusion. Ripples in the eyes and does not read at all. We now turn both queries into parameterized ones. In my opinion, very convenient. Instead of designs : N
SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`='bla-bla-bla'



    //...
    $sql = 'SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`=\''.$_GET['url_title'].'\'';
    DB::exec($sql);
    //...




    //...
    $sql = 'SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`=\''.mysql_real_escape_string($_GET['url_title']).'\'';
    DB::exec($sql);
    //...



    //...
    $sql = 'SELECT `firstname`,`lastname`,`nickname`,`avatar` FROM `users` WHERE `login`=\''.mysql_real_escape_string($_GET['username']).'\' AND `password`=\''.mysql_real_escape_string($_GET['password']).'\'';
    DB::exec($sql);
    //...






    //...
    $sql = 'SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`=:1';
    DB::exec($sql, $_GET['url_title']);
    //...


    //...
    $sql = 'SELECT `firstname`,`lastname`,`nickname`,`avatar` FROM `users` WHERE `login`=:1 AND `password`=:2';
    DB::exec($sql, $_GET['username'], $_GET['password']);
    //...


the corresponding argument is substituted. They, of course, can be duplicated and all that.
And what happens behind the scenes (the code is taken from one project , where I came up with [although the idea is not mine, but I did not know about it] is this )? Of course, regular expressions are not the best choice in terms of performance, but it suits me. You can attach a selection from a global context such as : glob: varname (for example : glob: _GET [id] ), but for this you need a smarter parser - for my purposes, it is not listed as being created. Or a separate parameter assignment as in Yii . However, this is a matter of taste. In my opinion, what is done by Yii does not solve the issue of readability, on the contrary: it’s more convenient than

public static function exec($query) {
    global $args;
    if(func_num_args() > 1) {
        $args = func_get_args();
        $query = preg_replace_callback(
            '/:([0-9]+)/',
            create_function(
                '$matches',
                'global $args; return "\'".str_replace("\'", "\\\\\'", @$args[$matches[1]])."\'";'
            ),
            $query
        );
    }
    self::$result = sqlite_query($query, self::$handle);
    return self::$result;
}



$username = mysql_real_escape_string($username);


$command->bindParam(":username",$username,PDO::PARAM_STR);


Advantages

• Readability Increases
• No separate filtering function required
• If you take such an appeal to the database as a style, then you can forget about SQL injections.

disadvantages

• In the beginning you will have to torment yourself in order to switch to the use of such queries.