SQL injection.

SQL injection is one of the most common methods of hacking websites and programs working with databases, based on the introduction of arbitrary SQL code into a query. With proper configuration of the database management system, the probability of successful implementation of SQL injections can be reduced to zero. But first things first. Let's start by describing the threat.
SQL injection, depending on the type of DBMS used and the conditions of implementation, may enable an attacker to perform an arbitrary query to the database (for example, read the contents of any tables, delete, modify, or add data), gain the ability to read and/or write local files, and execute arbitrary commands on the attacked server.
An SQL injection type attack may be possible due to incorrect processing of input data used in SQL queries.
The developer of application programs working with databases should be aware of such vulnerabilities and take measures to counteract the introduction of SQL.
Let's say the server software receives the input parameter id and uses it to create an SQL query. Consider the following PHP script:
…
$id = $_REQUEST['id'];
$res = mysql_query("SELECT * FROM news WHERE id_news = $id");
…
If the id parameter is passed to the server equal to 5 (for example, like this: http://example.org/script.php?id=5 ), then the following SQL query will be executed:
SELECT * FROM news WHERE id_news = 5
But if the attacker passes the string -1 OR 1=1 as the id parameter (for example, like this: http://example.org/script.php?id=-1+OR+1=1 ), then the request will be executed:
SELECT * FROM news WHERE id_news = -1 OR 1=1
Thus, changing the input parameters by adding SQL language constructs to them causes a change in the logic of executing the SQL query (in this example, instead of the news with the specified identifier, all available news in the database will be selected, since the expression 1=1 is always true).
Protection against SQL injection attacks.
In this post, fragments of the source code in various programming languages taken from an open source will be given as examples.
To protect against this type of attack, it is necessary to carefully filter the input parameters, the values of which will be used to build an SQL query.
Let's assume that the code generating the query looks like this:
statement := 'SELECT * FROM users WHERE name = "' + userName + '";';
To make code injection impossible, some databases, including MySQL, require putting all string parameters in quotation marks. In the parameter itself, replace the quotation marks with \", the apostrophe with \', and the backslash with \\ (this is called "escape special characters"). This can be done with the following code:
statement := 'SELECT * FROM users WHERE name = ' + QuoteParam(userName) + ';';
function QuoteParam(s : string) : string;
{ the input is a string; the output is a string in quotation marks and with replaced special characters }
var
i : integer;
Dest : string;
begin
Dest := '"';
for i:=1 to length(s) do
case s[i] of
'''' : Dest := Dest + '\''';
'"' : Dest := Dest + '\"';
'\' : Dest := Dest + '\\';
else Dest := Dest + s[i];
end;
QuoteParam := Dest + '"';
end;
Для PHP фильтрация может быть такой:
<?
$query = "SELECT * FROM users WHERE user='".mysql_real_escape_string($user)."';";
?>
Filtering of integer parameters.
Let's take another query:
statement := 'SELECT * FROM users WHERE id = ' + id + ';';
In this case, the id field has a numeric type and cannot be enclosed in quotation marks. Therefore, "quoting" and replacing special characters with escape sequences does not work. In this case, type checking helps; if the id variable is not a number, the query should not be executed at all.
For example, in Delphi, the code helps to counteract such injections.:
id_int := StrToInt(id);
statement := 'SELECT * FROM users WHERE id = ' + IntToStr(id_int) + ';';
In case of an error, the StrToInt function will raise an EConvertError exception, and an error message can be displayed in its handler. The double conversion ensures a correct response to numbers in the $132AB format (hexadecimal number system). On standard Pascal, which does not know how to handle exceptions, the code is somewhat more complicated.
For PHP, this method will look like this:
$query = 'SELECT * FROM users WHERE id = ' . (int) $id;
Truncation of input parameters.
To make changes to the logic of executing an SQL query, it requires the introduction of sufficiently long strings. So, the minimum length of the embedded string in the above examples is 8 characters ("1 OR 1=1"). If the maximum length of the correct parameter value is small, then one of the protection methods may be the maximum truncation of the input parameter values.
For example, if it is known that the id field in the above examples can take values of no more than 9999, you can "cut off the extra" characters, leaving no more than four:
statement := 'SELECT * FROM users WHERE id = ' + LeftStr(id, 4) + ';';
Oleg Petukhov, lawyer in the field of international law and personal data protection, information security specialist security, protection of information and personal data.
Telegram channel: https://t.me/protectioninformation Telegram Group: https://t.me/informationprotection1 Website: https://legascom.ru Email: online@legascom.ru #informationprotection #informationsecurity