SQL injection panacea - queries with parameters

Introduction

This topic is the beginning of a small cycle about panaceas for various web application vulnerabilities.

As it happens, a large number of Web applications suffer from SQL injections. And how many articles are not written, how many bug reports are not published - it’s all the same, programmers forget that the user can substitute whatever they want with the application parameters.

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

Implementation

Implementation is not more difficult than the idea. For example, let's take a request to get a blog post:
SELECT ` date `,`title`,`text`,`tags` FROM `posts` WHERE `url_title`= 'bla-bla-bla' <br/>

In PHP code, it will look something like this:
<?php <br/> //... <br/> $sql = 'SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`=\'' . $_GET [ 'url_title' ]. '\'' ;<br/> DB::exec( $sql );<br/> //... <br/>

The security problem is visible to the naked eye - the url_title parameter of the address bar is not filtered.
This fact is usually found when one of the visitors has already found it. B then the code is transformed into a more horrible look:
<?php <br/> //... <br/> $sql = 'SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`=\'' .mysql_real_escape_string( $_GET [ 'url_title' ]). '\'' ;<br/> DB::exec( $sql );<br/> //... <br/>

Some queries look especially awful:
<?php <br/> //... <br/> $sql = 'SELECT `firstname`,`lastname`,`nickname`,`avatar` FROM `users` WHERE `login`=\'' .mysql_real_escape_string( $_GET [ 'username' ]). '\' AND `password`=\'' .mysql_real_escape_string( $_GET [ 'password' ]). '\'' ;<br/> DB::exec( $sql );<br/> //... <br/>

Even when I printed this safe example, I made a mistake and made a syntax error somewhere in the area of ​​quotes and escape slashes.
The larger the request, the more confusion. In the eyes dazzled and completely unreadable.

We now turn both queries into parameterized ones.
<?php <br/> //... <br/> $sql = 'SELECT `date`,`title`,`text`,`tags` FROM `posts` WHERE `url_title`=:1' ;<br/> DB::exec( $sql , $_GET [ 'url_title' ]);<br/> //... <br/>

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

In my opinion, very convenient. Instead of constructions : N is substituted for the corresponding argument. 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 didn’t know about it] that )?

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

Of course, regular expressions are not the best choice in terms of performance, but it suits me. You can attach a sample from a global context of the type : glob: varname (for example ,: glob: _GET [id] ), but for this you need a parser more intelligently - for my purposes, creating one is not listed. Or a separate assignment of parameters as in Yii . However, this is a matter of taste. In my opinion, what is done Yii does not solve the issue of readability, on the contrary:
$username = mysql_real_escape_string( $username ); <br/>
more convenient than
$command->bindParam( ":username" , $username , PDO ::PARAM_STR); <br/>

Virtues

• Readability increases
• No separate filtering function required.
• If you accept such an appeal to the database for style, then you can forget about the SQL injection.

disadvantages

• In the beginning you will have to suffer in order to switch to the use of such requests.