Dynamic Queries In SQL Injection By albro

In this post, I want to talk about queries that are created dynamically.

Dynamic Queries

One of the issues that needs to be investigated is the issue of creating complex queries. One of the good examples in this field is the advanced search section of the sites. For example, some online stores allow you to search with different factors (product color, price, company name, etc.). In this case, our query is created dynamically according to the user's request. In other words, wherever parts of the SQL code are added to the query according to the user's request or parts are removed from it, they are placed in this category.

In such cases, it's not possible to use placeholders, so we need another mechanism. If the desired part of your site (for example, advanced search) does not have many complications, you can use the query builder. The action mechanism of these query builders is as follows:

$query = $users = DB::table('users')->select('*');
if ($fname = input::get('first_name'))
{
    $query->where('first_name = ?', $fname);
}
if ($lname = input::get('last_name'))
{
    $query->where('last_name = ?', $lname);
}
// etc......
$results = $query->get();

But most of the time we have queries that are much more complex and using query builders becomes so difficult that it no longer has a rational justification. We always know that all the dynamic parts of a query are entered into the main query with placeholders, but for this case, I know a very good trick:

On stackoverflow website, a question was asked in this regard and the answer (although it is simple) is one of the smartest and very good answers that I have seen in these few years. The questioner intends to design a search system on the website, whose various factors are determined by the users. On the other hand, the user may leave some of these factors (for example, age is not important). The questioner wants to know how it is possible to create a query in this situation that has all the different states (such as leaving the age and age by the user) and is also executable. The example mentioned by the questioner is as follows:

$sql = 'SELECT * FROM people WHERE first_name = :first_name AND last_name = :last_name AND age = :age AND sex = :sex';
$query = $db->prepare($sql);
$query->execute(array(':first_name' => 'John', ':age' => '27');

Received the following code as an answer:

SELECT * FROM people 
WHERE (first_name = :first_name or :first_name is null)
AND (last_name = :last_name or :last_name is null)
AND (age = :age or :age is null)
AND (sex = :sex or :sex is null)

If we give the null parameter to the abandoned items, there is no need to worry. Of course, in this case, if you use PDO, the emulation mode must be ON. In fact, looking at the code above, you'll notice that it is enough to bind our variables to placeholders (either they have a value or they become null). If we do this, those with null values will be discarded and only those with a certain value will be included in the query.

However, always remember that the final query must be built from only two sources: constants and placeholders. Therefore, in summary, any SQL query can only be made of two types of data:

• Fixed sections manually written into the script.
• Use placeholders for dynamic values

If you follow this rule you will be safe against SQL injection.

Common Mistakes

Some of the common mistakes of programmers in this field are as follows:

• Escaping user data: This is one of the biggest mistakes programmers make. Escaping user data has two problems:
  • Escaping: Escaping only provides part of the protection for a literal type, and you will not be protected from SQL injection by doing it alone, but if you don't use it in the right place, you will hurt your program.
  • Data or user input: Any variable that exists can be dangerous, whether it comes from the user or not. Therefore, every variable must be formatted before it is included in the query, whether it is from the user side or from somewhere else.
• magic quotes: Never use this feature. This feature was exactly the implementation of the above mistakes (escaping user data), which fortunately has now been removed from the SQL language. If you don't know what this feature is, what's better! Don't waste your time!
• Data validation: Data validation in forms has nothing to do with SQL and the security of our database. In fact, we can control the basic things, but be sure that form validation cannot protect you from the risk of SQL injection. Do you remember the example of Leo O'Hara? This name is completely correct and valid and appears valid during validation, but it caused a problem in our program.
• htmlspecialchars (also things like filter_var() and strip_tags()): as the name suggests! It's called HTML, which means it's not related to SQL, and you shouldn't associate it with SQL injection. All these things that I mention have their own functions and I am not saying that they are useless, but I am saying that they are unimportant in the field of SQL injection protection. SQL formatting should never change data! For example, when you put your jewelry in the safe to protect them, you expect to take the same jewelry intact later, not that a part of it has changed! The same is true in the SQL language; The job of a database is to store data, not to protect and change it.
• Use of a function for sanitization of all data: Our data is used in different contexts (SQL queries, HTML codes, JS codes, JSON codes, etc.). For this reason, we cannot clean everyone in the same way. You must clear your data for each specific field separately so that there is no problem in your application.
• Using separate databases to execute DML queries: This item will not help you either. In fact, this method tries to prevent our information from falling into the hands of unknown people if our database is hacked and SQL Injection is performed. Therefore, protection against SQL Injection is not considered (besides, it may consume our costs and time unnecessarily).

In several posts, I tried to focus my attention on the topic of SQL Injection (as a topic independent of different tools) so that the topics are general and generalizable and you can implement it in MySQL or PDO or whatever method you have.

Thank you for being with me and I hope these posts have helped you and your website security. Looking forward to your comments!