SQL injection is a widespread method of attacking web applications using database systems.
With SQL injections, malicious users are trying to exploit vulnerabilities in the security of a web application to interfere with database data. As a method of attack, it is neither new nor complex. For SQL injection, just a web browser and common sense is enough, so SQL injection attacks occurred simultaneously with the first web applications.
The purpose of SQL injection injections is to exploit specific weaknesses of a web application to execute SQL queries that are not predicted by developers in the database.
Examples of such queries are the display of (SELECT) data that is not intended to be displayed to users, the uninterrupted input (INSERT) of data on the database, and the deletion of (DELETE) data from the database.
Despite the increased risk, the number of systems that are vulnerable to SQL Injections attacks is now high. The pleasure is that as a threat it can easily be explored in an application and with proper prediction to be avoided.
In SQL injections, a malicious user attempts to exploit the incomplete or incorrect validation of an application's input data. Typically, the vulnerability of SQL injections can also occur in non-web applications, but there is a lot more rare. Typical ways users give input to a web application are forms (with HTTP GET and POST methods) and links (HTTP GET method). In the case of incomplete verification of the input data it is sometimes possible to pass extra SQL commands to run in the application or to change parts of a SQL query in a way that is not predicted.
Consider the case of viewing an employee's data from a table named "employees" in a MySQL database.
The following (vulnerable) PHP block code executes a SELECT query in the database for the purpose of reading the data of a particular employee from the database.
<?php
$qry = "SELECT employeeid, fullname, salary FROM employees " .
"WHERE employeeid =" . $_GET['employeeid'];
$result = mysql_query($qry);
?>
The purpose of the developer here is to execute form queries:
SELECT employeeid, fullname, salary FROM employees WHERE employeeid = 3
SELECT employeeid, fullname, salary FROM employees WHERE employeeid = 352
SELECT employeeid, fullname, salary FROM employees WHERE employeeid = 590
where employeeid (the primary key in the employees table) is a value given in practice by the application user (through the browser, using the HTTP GET method). For example, in the typical case, the employeeid can be given a link of the form:
http://www.example.com/employees.php?employeeid=3
The problem is that the value of the GET "employeeid" parameter given in the URL is NOT adequately verified before the code execution is executed.
So a malicious user can write the following URL (manually) in the browser:
http://www.example.com/employees.php?employeeid=3 OR 1=1
where it will result in the following query being executed in the database:
SELECT employeeid, fullname, salary FROM employees WHERE employeeid=3 OR 1=1
but the WHERE branch will apply to each employee record, so all entries are returned to the $ result variable.
Depending on how the $ result variable (to present the data to web app users) is then used, malicious user may see information that is inappropriate.
Let's look at the following (fragile) PHP block code for login in an application that uses the values given by users in a web form (username and password) to enter the system:
<?php
$username = $_POST['username'];
$password = $_POST['password'];
$qry = "SELECT userid FROM users" .
" WHERE username='$username' AND password='$password'";
$result = mysql_query($qry);
if (mysql_numrows($result) > 0) {
//log in user...
}
?>
Username and password values are given using a standard web login form (using the HTTP POST method). If the malicious user in the password field give the price:
bar' OR 1=1 OR username='
then from the above PHP code we have the query executed:
SELECT userid FROM users WHERE
username='foo' AND password='bar' OR 1=1 OR username='';
but this holds true for every record on the table (always having 1 = 1), so $ result will always have records, regardless of what is entered in the username and password fields. Then the $ results variable is checked for whether it contains records (that will contain it), then login the user.
This may allow access to the application by people who are not authorized to do so.
In this example and in the previous one, we saw that a malicious user might try to change a SQL query in a way that was not predicted by the developer. Of course, the query could be changed in different ways than in the specific examples (typical example is the attempt to use SQL UNION branches).
This example exploits the ability to run multiple SQL statements as a query in the system. Are commands separated by the character? (semicolon - Greek question mark). Running multiple commands as a query is a standard feature in the database space, but it is the greatest risk in the case of SQL Injections.
We will build on example 1 and consider that the database system is PostgreSQL. We have the following (vulnerable) block code:
<?php
$qry = "SELECT employeeid, fullname, salary FROM employees " .
"WHERE employeeid =" . $_GET['employeeid'];
$result = pg_query($qry);
?>
the programmer expects input data from form links:
http://www.example.com/employees.php?employeeid=3
which will result in the query being executed:
SELECT employeeid, fullname, salary FROM employees
WHERE employeeid = 3
but this time the malicious user gives the following URL (manually) to the browser:
http://www.example.com/employees.php?employeeid=3;DELETE FROM users;
and so the following 2 commands will be executed in the pg_query () call:
SELECT employeeid, fullname, salary FROM employees
WHERE employeeid = 3;
DELETE FROM users;
where it will delete all user table data from the database!
Of course, instead of the DELETE of the example, any other SQL command could be executed in this case (and this is also a good example of why it is not safe to link our application to the database with a user of the database with elevated rights access - with increased access permissions one could delete up to other non-web-related database systems).
Until version 4.1 of MySQL, multiple commands could not be executed as a programming language query. From version 4.1 onwards, this feature was added. The PHP programming language has chosen to continue not to support multiple commands separated by? in the mysql_query () function.
Unsupported multiple commands as a PHP query is in fact a language weakness, since multiple commands are faster and more desirable, such as bulk UPDATE and bulk INSERT. Security against SQL injection attacks must have the code of an application and is the responsibility of the developer. In PHP5 the mysql interface (mysql improved interface) supports multiple commands using the multi_query () method.
Most programming languages (such as Perl, Java, Python, C) offer multiple commands for MySQL 4.1+ versions. In a few cases, this support must be enabled on the base. For example, in the case of Java JDBC, the connection must be made with the following DSN:
"Jdbc: mysql: //127.0.0.1: 3306 / databasename? AllowMultiQueries = true"
while in Perl DBI we have respectively:
"DBI: mysql: databasename; host = 127.0.0.1; port = 3306; mysql_multi_statements = 1"
In other database systems (except for MySQL, such as: SQLite, PostgreSQL, Oracle, MS SQL Server), multiple orders (and PHP 4 and PHP 5) are normally supported.