SQL injection

SQL injection (dt. SQL injection ) is the exploitation of a vulnerability associated with SQL databases, by lack of masking or review of meta characters created in user input. The attacker tries to smuggle in his own database commands via the application that provides access to the database . Its aim is to spy out data, to change it in accordance with its needs, to gain control over the server or simply to cause the greatest possible damage.

Occur

SQL injections are possible when data such as user input get into the SQL interpreter. This is because user inputs can contain characters that have special functions for the SQL interpreter and thus enable external influence on the database commands executed. Such meta characters in SQL are, for example, the backslash (backslash), the quotation marks , the apostrophe and the semicolon .

Such gaps can often be found in CGI scripts and in programs that enter data such as website content or e-mails into SQL databases. If such a program does not mask correctly, an attacker can smuggle in additional SQL commands through the targeted use of function characters or manipulate the queries in such a way that additional data is changed or output. In some cases it is also possible to gain access to a shell , which usually means the possibility of compromising the entire server.

process

Change of data

The find.cgi script for displaying articles is located on a web server . The script accepts the parameter "ID", which later becomes part of the SQL query. The following table should illustrate this:

	Expected call
call	http://webserver/cgi-bin/find.cgi?ID=42
Generated SQL	SELECT author, subject, text FROM artikel WHERE ID=42;
	SQL injection
call	http://webserver/cgi-bin/find.cgi?ID=42;UPDATE+USER+SET+TYPE="admin"+WHERE+ID=23
Generated SQL	SELECT author, subject, text FROM artikel WHERE ID=42;UPDATE USER SET TYPE="admin" WHERE ID=23;

A second SQL command is slipped into the program, which modifies the user table.

Change the database server

The search.aspx script for searching for websites is located on a web server. The script accepts the parameter "keyword", the value of which will later become part of the SQL query. The following table should illustrate this:

	Expected call
call	http://webserver/search.aspx?keyword=sql
Generated SQL	SELECT url, title FROM myindex WHERE keyword LIKE '%sql%'
	SQL injection
call	http://webserver/search.aspx?keyword=sql'+;GO+EXEC+cmdshell('shutdown+/s')+--
Generated SQL	SELECT url, title FROM myindex WHERE keyword LIKE '%sql' ;GO EXEC cmdshell('shutdown /s') --%'

Another command is appended to the actual query. The two hyphens --comment out the apostrophe as a remnant of the actual request, which means that it is ignored. The query that has now been generated enables a Windows process to be executed, illustrated here by the forced shutdown of the server (provided the process has administrator rights). But data or the like can also be generated in this way (using the example of Microsoft SQL Server).

Spy on data

The UNIONclause is available on some SQL implementations . This allows several SELECTs to be sent at the same time, which then return a common set of results. UNIONAny tables and system variables can be spied on using a cleverly subordinated clause .

	Expected call
call	http://webserver/cgi-bin/find.cgi?ID=42
Generated SQL	SELECT author, subject, text FROM artikel WHERE ID=42;
	SQL injection
call	http://webserver/cgi-bin/find.cgi?ID=42+UNION+SELECT+login,+password,+'x'+FROM+user
Generated SQL	SELECT author, subject, text FROM artikel WHERE ID=42 UNION SELECT login, password, 'x' FROM user;

The xwhen UNION SELECTis necessary because all with UNIONassociated SELECTs the same number must have columns. The attacker can find out the number of columns by ID=42 order by x--appending. For example, if the page loads normally at x = 8, but shows an error message or another page at x = 9, then the number of columns is 8.

If the database server is configured incorrectly and if, for example, a user who is currently connected to the database and via whom the SQL injection is to be sent has access to system databases, the attacker can use simple SQL syntax to Systemdatenbank.SystemtabelleMitTabellenAuflistungaccess the system tables and all tables of a specific one Read out database. This allows him to get important information to carry out further attacks and penetrate deeper into the system.

In the case of MySQL database systems, this system information is managed in the database information_schema. The following example shows how the structure of all accessible databases can be found in a query with 3 result columns.

	Expected call
call	http://webserver/cgi-bin/find.cgi?ID=42
Generated SQL	SELECT author, subject, text FROM artikel WHERE ID=42;
	SQL injection
call	http://webserver/cgi-bin/find.cgi?ID=42+UNION+SELECT+'Datenbank',+'Tabelle',+'Spalte'+ UNION+SELECT+table_schema,+table_name,+column_name+FROM+information_schema.columns+ WHERE+NOT+table_schema='information_schema';#%20
Generated SQL	SELECT author, subject, text FROM artikel WHERE ID=42 UNION SELECT 'Datenbank', 'Tabelle', 'Spalte' UNION SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE NOT table_schema='information_schema';# ;

Some database systems still offer the option of returning files via a request. In combination with the above-mentioned techniques and if the path is known, any files to which the database process has access can be read out.

Infiltration of any code

A lesser-known variant is also potentially the most dangerous. If the database server supports the commands SELECT … INTO OUTFILEor SELECT … INTO DUMPFILE, these commands can be used to store files on the database server's file system . Theoretically, if the library directory of the operating system or the database server is writable by the same (if this is running as root , for example ), it is possible to execute any code on the system.

Time-based attacks

If the database server supports benchmark functions, the attacker can use these to obtain information about the database structure. In connection with the if construct, there are hardly any limits to the attacker's creativity.

The following example takes several seconds on a MySQL database server if the current user is root :

SELECT IF( USER() LIKE 'root@%', BENCHMARK(100000,SHA1('test')), 'false');

Obtaining administrator rights

With certain database servers, such as Microsoft SQL Server up to version 2000, stored procedures such as Xp_cmdshell were automatically offered which, among other things, could be misused to execute commands with the rights of the SQL server program. Newer versions of Microsoft SQL Server have disabled this function by default. This option could be used to start a shell on the attacked computer , for example .

Vulnerabilities within the database server

Sometimes vulnerabilities also exist within the database software itself. For example, the PHP function mysql_real_escape_string () in the MySQL server allowed an attacker to carry out SQL injection-based attacks based on Unicode characters even if the user input was correctly escaped. This bug was corrected in version 5.0.22 on May 24, 2006.

Blind SQL injection

A blind SQL injection is used when a server does not return a descriptive error message indicating whether the submitted query was successfully executed or not. On the basis of various small things, such as slightly different error messages or characteristically different response times from the server, an experienced attacker can often determine whether a query was successful or reports an error.

Countermeasures

In order to prevent SQL injections, data entered by the user must not simply be incorporated into an SQL statement. Input data can be checked for the properties of expected values. For example, German postcodes only consist of digits.

The safest way, however, is to keep the data away from the SQL interpreter at all. You can do without clipping the input. The technology for this are bound parameters in prepared statements . The data are transferred as parameters to an already compiled command. The data is therefore not interpreted and an SQL injection is prevented. As a positive side effect, you also get an increase in performance with certain databases (e.g. Oracle ). Stored procedures , on the other hand, do not offer any general protection against SQL injection, especially not if the SQL code of the function is not known.

However, safety precautions can also be taken on the database server side. The users with which a web application authenticates itself to the database server should only have the privileges that it actually needs. This can render at least some of the possible attacks ineffective.

If the operator of a web server has no control over the applications, the use of web application firewalls (WAF) can at least partially prevent SQL injection vulnerabilities from being exploited. Regardless of the control over the applications, an operator of a web server can additionally increase security through the targeted use of a WAF, since many WAFs offer prophylactic measures in addition to defensive measures.

It is not difficult to rebuild existing programs so that SQL injections are no longer possible. The main problem for most programmers is a lack of knowledge of these types of attacks. Below are a few examples to help ward off the attacks.

python

There are several ways to communicate with a database in Python. One of them is SQLAlchemy . In order to avoid SQL injection, raw SQL commands should be avoided if they could be manipulated by form or URL requests. One such example is shown below:

db.engine.execute("SELECT * FROM table")

Instead, it is recommended to use the internal functions and methods of SQLAlchemy, such as B. the following:

session.query(Order).get(order_id)
session.query(Order).filter(Order.status == 'active')

Visual Basic (ADOdb)

There are simple Command objects in Visual Basic that can be used to avoid these problems.

Instead of

cn.Execute "SELECT spalte1 FROM tabelle WHERE spalte2 = '" & spalte2Wert & "'"

the following should be used:

Dim cmd As ADODB.Command, rs as ADODB.Recordset
With cmd
  Set .ActiveConnection = cn
  Set .CommandType = adCmdText
  .CommandText = "SELECT spalte1 FROM tabelle WHERE spalte2 = ?"
  .Parameters.Append .CreateParameter("paramSp2", adVarChar, adParamInput, 25, spalte2Wert) '25 ist die max. länge
  Set rs = .Execute
End With

Delphi

The queries have known parameters since the BDE. The syntax for different component collections is not always identical but similar.

Instead of...

function TDatabase.GetData(ParameterID: Integer): Integer;
var
  Qry: TQuery;
begin
  Result := 0;
  Qry := TQuery.Create;
  try
    Qry.SQL.Text := 'SELECT F_DATA FROM T_BLUBB WHERE ID = ' + IntToStr(ParameterID);
    Qry.Open;
    Result := Qry.FieldByName('F_DATA').AsInteger;
  finally
    Qry.Free;
  end;
end;

... you should definitely work with parameters:

function TDatabase.GetData(ParameterID: Integer): Integer;
var
  Qry: TQuery;
begin
  Result := 0;
  Qry := TQuery.Create;
  try
    Qry.SQL.Text := 'SELECT F_DATA FROM T_BLUBB WHERE ID = :PI';
    Qry.ParamByName('PI').AsInteger := ParameterID;
    Qry.Open;
    Result := Qry.FieldByName('F_DATA').AsInteger;
  finally
    Qry.Free;
  end;
end;

Microsoft .NET Framework - C # (ADO.NET)

In the .NET Framework, there are simple objects with which such problems can be avoided.

Instead of

SqlCommand cmd = new SqlCommand("SELECT spalte1 FROM tabelle WHERE spalte2 = '"
                 + spalte2Wert + "';");

the following should be used:

string spalte2Wert = "Mein Wert";
SqlCommand cmd = new SqlCommand("SELECT spalte1 FROM tabelle WHERE spalte2 = @spalte2Wert;");
cmd.Parameters.AddWithValue("@spalte2Wert", spalte2Wert);

Java (JDBC)

An SQL injection can easily be prevented by an existing function. In Java , the PreparedStatement class is used for this purpose ( JDBC technology) and the data of uncertain origin are transferred as separate parameters. The placeholder "?" Is used to separate the data from the SQL statement.

Instead of

Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("SELECT spalte1 FROM tabelle WHERE spalte2 = '"
                 + spalte2Wert + "';");

the following should be used:

PreparedStatement pstmt = con.prepareStatement("SELECT spalte1 FROM tabelle WHERE spalte2 = ?");
pstmt.setString(1, spalte2Wert);
ResultSet rset = pstmt.executeQuery();

The additional paperwork required by using the PreparedStatement class can also pay off in terms of performance if the program uses the PreparedStatement object multiple times.

PHP

The PHP Data Objects library is available in PHP for database access.

Example without prepared statement:

$dbh->exec("INSERT INTO REGISTRY (name, value)
            VALUES (".$dbh->quote($name,PDO::PARAM_STR).", ".$dbh->quote($value,PDO::PARAM_INT).")");

Example with prepared statement:

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Up to PHP version 5.3 there was the configuration option "magic_quotes_gpc". If this was set to "on", external user input was automatically masked. Some scripts use functions such as addslashes()or mysql_real_escape_string(). This means that a backslash has already been placed in front of all relevant characters in the user input using so-called magic quotes , and a backslash is now placed in front of them using the escape function. This falsifies the user input and instead of a single quotation mark you get a quotation mark with a preceding backslash ( \"). For reasons of portability , too, this setting should not be used when developing applications and instead all entries should be manually validated and masked, as it cannot be assumed that the same settings will prevail or are possible on all systems. In addition, addSlashes()database entries should not be masked as this does not mysql_real_escape_string()guarantee adequate security .

After the PHP version 5.3 it was mysql_real_escape_string()replaced by MySQLi . From version 7.0 is mysql_real_escape_string()no longer available. From PHP 7.x the function is called mysqli_real_escape_string().

Pearl

With the database-independent database module DBI , which is normally used in Perl :

Instead of

$arrayref = $databasehandle->selectall_arrayref("SELECT spalte1 FROM tabelle WHERE spalte2 = $spalte2Wert");

the following should be used:

$arrayref = $databasehandle->selectall_arrayref('SELECT spalte1 FROM tabelle WHERE spalte2 = ?',{},$spalte2Wert);

Perl's DBI module also supports a “prepare” syntax similar to that in the Java example.

$statementhandle = $databasehandle->prepare("SELECT spalte1 FROM tabelle WHERE spalte2 = ?");
$returnvalue = $statementhandle->execute( $spalte2Wert );

Alternatively, input values ​​can also be safely masked using the database handle. The DB driver pays attention to the special characters typical for this database. The programmer does not need to have any in-depth knowledge of it.

$arrayref = $databasehandle->selectall_arrayref("SELECT spalte1 FROM tabelle WHERE spalte2 = " .
						$databasehandle->quote($spalte2Wert) );

In the so-called "taint mode", which can be activated script-wide with the -T parameter , Perl uses strong heuristics to only allow secure access. Character strings that contain parameters passed by the user are initially treated as "unsafe" until the data has been explicitly validated and must not be used in unsafe commands beforehand.

ColdFusion Markup Language

The <cfqueryparam>tag can be used under ColdFusion, which does all the necessary validations:

   SELECT * FROM courses WHERE Course_ID =
   <cfqueryparam value = "#Course_ID#" CFSQLType = "CF_SQL_INTEGER">

MS-SQL

The database can be protected against SQL injections using parameterized commands:

SELECT COUNT(*) FROM Users WHERE UserName=? AND UserPasswordHash=?

See also

• Web application security

Web links

• SQL Injections Analysis
• Abusing Poor Programming Techniques in Web server scripts via SQL Injection (English)
• Web application security: catalog of measures and best practices (PDF; 805 kB) Federal Office for Information Security (BSI)
• Web Security Threat Classification (PDF, 432 kB)
• Steve Friedl: SQL Injection Attacks by Example .
• Advanced SQL Injection in MySQL (PDF; 935 kB)
• SQL injection in the comic xkcd ( xkcd.com )

References and resources

1. ^ The INFORMATION_SCHEMA database . MySQL 5.1 Reference Manual, Chapter 20
2. ↑ SQL_Injection_Prevention_Cheat_Sheet.md. Retrieved October 24, 2019 . 
3. ↑ 10 Reasons to love SQLAlchemy. In: pajhome.org.uk. Retrieved December 20, 2016 . 
4. ↑ addslashes PHP Manual
5. ↑ mysql_real_escape_string PHP Manual
6. ↑ Magic Quotes PHP Manual
7. ↑ Chris Shiflett: addslashes () Versus mysql_real_escape_string () (English)
8. ↑ ColdFusion Online Help ( Memento November 24, 2006 in the Internet Archive )

This version was added to the list of articles worth reading on July 30, 2005 .