Referring to owasp, this paper focuses on providing clear, simple and operable guidance to prevent SQL injection vulnerabilities in applications. Unfortunately, SQL injection attacks are common due to two factors:
-
Significant prevalence of SQL injection vulnerabilities
-
The attractiveness of the target (i.e. the database usually contains all the interesting / key data of the application).
It's a bit shameful to have so many successful SQL injection attacks, because it's very simple to avoid SQL injection vulnerabilities in your code.
SQL injection vulnerabilities are introduced when software developers create dynamic database queries that contain user supplied input. It's easy to avoid SQL injection defects. Developers need to:
a) Stop writing dynamic queries;
b) Prevent user supplied input containing malicious SQL from affecting the logic of the executed query.
This article provides a set of simple techniques to prevent SQL injection vulnerabilities by avoiding these two problems. These technologies can be used with almost any type of database. Other types of databases, such as XML databases, may have similar problems (for example, XPath and XQuery injection), and these technologies can also be used to protect them.
Main defense:
-
Option 1: use prepared statements (with parameterized queries)
-
Option 2: using stored procedures
-
Option 3: white list input verification
-
Option 4: escape all user supplied inputs
Additional defense:
-
Also: enforce minimum permissions
-
In addition: perform white list input verification as an auxiliary defense
Examples of insecurity:
SQL injection vulnerabilities are usually as follows:
The following (Java) example is UNSAFE and allows an attacker to inject code into a query to be executed by the database. The unauthenticated "customerName" parameter simply attached to the query allows attackers to inject any SQL code they want. Unfortunately, this method of accessing databases is too common.
String query = "SELECT account\_balance FROM user\_data WHERE user_name = "
+ request.getParameter("customerName");
try {
Statement statement = connection.createStatement( ... );
ResultSet results = statement.executeQuery( query );
}
...
Main defense
====
Defense option 1: prepared statement (with parameterized query)#
=====================
Using pre prepared statements with variable binding (that is, parametric queries) is the first method that all developers should learn how to write database queries. They are easier to write and understand than dynamic queries. Parameterized queries force developers to define all first SQL Code, and then pass each parameter to the query. This coding style allows the database to distinguish between code and data, no matter what user input is provided.
The prepared statement ensures that the attacker cannot change the intention of the query, even if the attacker inserts SQL The same is true of orders. In the following security example, if the attacker enters userID tom' or '1'='1，The parameterized query will not be attacked, but will find the user name that exactly matches the string tom' or '1'='1.
Language specific recommendations:
* Java EE - PreparedStatement()Use with bound variables
* .NET - Use parameterized queries, such as binding variables SqlCommand()or OleDbCommand()Using bound variables
* PHP - take PDO Use with strongly typed parameterized queries (using bindParam())
* Hibernate - createQuery()Use with bound variables (in Hibernate (called named parameter in)
* SQLite - be used for sqlite3_prepare()Create statement object
In rare cases, prepared statements can damage performance. In this case, it is best to a)Strongly validate all data or b)Use database vendor specific escape routines to escape all user supplied input, as described below, instead of using prepared statements.
security JavaSQL Statement example
The following code example uses PreparedStatementJava Parameterized query implementation to execute the same database query.
//Be sure to verify that String custname = request getParameter(“customerName”);
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);
ResultSet results = pstmt.executeQuery( );
```
security C＃.NET SQL Statement example
use.NET，It's more direct. The creation and execution of queries do not change. All you have to do is use Parameters.Add()The call shown here passes parameters to the query.
```
String query = "SELECT account\_balance FROM user\_data WHERE user_name = ?";try {
OleDbCommand command = new OleDbCommand(query, connection);
command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));
OleDbDataReader reader = command.ExecuteReader(); // ...} catch (OleDbException se) { // error handling}
We are already Java and.NET Examples are shown in, but virtually all other languages (including Cold Fusion and Classic ASP)All support parameterized query interfaces. even to the extent that SQL Abstraction layer, such as Hibernate Query language( HQL)There is also the same type of injection problem (we call it HQL Injection). HQL Parametric queries are also supported, so we can avoid this problem: Hibernate Query language( HQL)Prepare statement (named parameter) example
//First is an unsafe HQL StatementQuery unsafeHQLQuery = session.createQuery("from Inventory where productID='"+userSuppliedParameter+"'");//Here is a safe version of the same query using named parametersQuery safeHQLQuery = session.createQuery("from Inventory where productID=:productid");
safeHQLQuery.setParameter("productid", userSuppliedParameter);
```
Developers tend to like Prepared Statement Method, because all SQL All remain in the application code. This makes your application relatively database independent.
Defense option 2: stored procedures#
===========
SQL Injection is not always a safe stored procedure. However, some standard stored procedure programming structures have the same effect as using parametric query in secure implementation, which is the standard of most stored procedure languages.
They require developers to build with only automatically parameterized parameters SQL Statement, unless the developer largely exceeds the standard. The difference between a prepared statement and a stored procedure is that the stored procedure SQL The code has been defined and stored in the database itself and then invoked from the application. These two technologies are preventing SQL Injection has the same effect, so your organization should choose which method makes the most sense to you.
be careful:'Security implementation'This means that stored procedures do not contain any unsafe dynamic information SQL Generate. Developers typically do not generate dynamic in stored procedures SQL. However, it can be done, but it should be avoided. If this cannot be avoided, the stored procedure must use input validation or the correct escape described in this article to ensure that all user supplied stored procedure inputs cannot be used SQL Code is injected into dynamically generated queries. Auditors should always be SQL Server Find in stored procedure sp_execute，execute or exec Usage of. Similar audit guidelines are necessary for similar functions of other suppliers.
In some cases, stored procedures may increase risk. For example, MS SQL On the server, you have three main default roles: db_datareader，db_datawriter and db_owner. Before the stored procedure is used, DBA On request webservice Provided by users db\_datareader or db\_datawriter jurisdiction. However, the stored procedure requires execution permission, and this role is not available by default. User management has focused on some settings, but is limited to these three roles, resulting in all Web Application in db_owner Run under permissions, so the stored procedure can work normally. Of course, this means that if the server is compromised, the attacker has full access to the database. Previously, they might only have read access.
security Java Stored procedure example
The following code example uses CallableStatementJava The stored procedure interface is implemented to execute the same database query. Should sp_getAccountBalance Stored procedures will be predefined in the database and perform the same functions as the queries defined above.
```
// This should REALLY be validatedString custname = request.getParameter("customerName");
try {
CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");
cs.setString(1, custname);
ResultSet results = cs.executeQuery();
// ... result set handling } catch (SQLException se) {
// ... logging and error handling}
safe VB .NET Stored procedure example The following code example uses SqlCommand.NET The stored procedure interface is implemented to execute the same database query. Should sp_getAccountBalance Stored procedures will be predefined in the database and perform the same functions as the queries defined above.
Try
Dim command As SqlCommand = new SqlCommand("sp_getAccountBalance", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text))
Dim reader As SqlDataReader = command.ExecuteReader() '...
Catch se As SqlException
'error handling
End Try
Defense option 3: white list input verification# ============== SQL Parts of the query are not legal places to use bound variables, such as table or column names and sort order indicators( ASC or DESC). In this case, input validation or query redesign is the most appropriate defense. For table or column names, ideally these values come from code, not from user parameters. However, if the user parameter value is used to make the table name and column name different, the parameter value should be mapped to the legal value/The expected table or column name to ensure that unauthenticated user input does not end in the query. Please note that this is a symptom of poor design and should be considered for complete rewriting if time permits. The following is an example of table name validation.
String tableName;switch(PARAM): case "Value1": tableName = "fooTable"; break; case "Value2": tableName = "barTable"; break;
... default : throw new InputValidationException("unexpected value provided"
+ " for table name");
Should tableName You can then attach directly to SQL The query, as it is currently known, is one of the legal and expected values of this query table name. Remember that common table validation can lead to data loss because table names are used in queries that do not expect them. For something as simple as sort order, it's best to convert user supplied input to a Boolean value, and then use the Boolean value to select the security value to attach to the query. This is a very standard requirement in dynamic query creation. For example:
public String someMethod(boolean sortOrder) { String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" : "DESC");`
...
```
User input can be converted to non-linear at any time String，Such as date, number, Boolean value, enumeration type, etc., and then append it to the query, or use it to select the value to append to the query, which can ensure that it is safe to do so.
In all cases, it is also recommended that input validation be used as a secondary defense, even if binding variables are used, as described later in this article. More techniques on how to implement strong whitelist input verification are described in the input verification memo.
Defense option 4: escape all user supplied inputs#
==================
When none of the above methods is feasible, the technology should only be used as a last resort. Input validation may be a better choice, because this method is fragile compared with other defenses, and we can't guarantee that it will prevent all in all cases SQL Inject.
This technique is to escape user input before putting it into the query. Its implementation is very specific in database. It is generally only recommended that implementing input validation does not reduce the cost-effectiveness of legacy code. You should use parameterized queries, stored procedures, or some kind of object relational mapper that builds queries for you( ORM)To build or rewrite applications built from scratch or applications that require low risk tolerance.
This technology is like this. each DBMS All support one or more character escape schemes specific to some queries. If you escape all user supplied input using the correct escape scheme for the database you are using, then DBMS This input will not be compared to the one written by the developer SQL Code confusion to avoid any possible SQL Injection vulnerability.
To search for a database encoder specifically javadoc，Please click Codec The class on the left. There are many codec implementations. Two database specific codecs are OracleCodec，and MySQLCodec.
Just All Known Implementing Classes:stay Interface Codec Click its name at the top of the page.
At present, ESAPI Currently has the following database encoders:
* MySQL(support ANSI And native mode)
Database encoder coming soon:
* SQL Server
* PostgreSQL of
If your database encoder is missing, please let us know.
Database specific escape details#
==============
If you want to build your own escape routine, here's what we do for you ESAPI Escape details of each database developed by encoder:
* SQL Server
* DB2
Escape dynamic query
======
use ESAPI Database codec is very simple. Oracle Examples are as follows:
```
ESAPI.encoder().encodeForSQL( new OracleCodec(), queryparam );
```
Therefore, if you generate an existing dynamic query in your code, the query goes to Oracle，As follows:
```
String query = "SELECT user\_id FROM user\_data WHERE user_name = '"
+ req.getParameter("userID")
+ "' and user_password = '" + req.getParameter("pwd") +"'";try { Statement statement = connection.createStatement( ... ); ResultSet results = statement.executeQuery( query );
}
You will rewrite the first line to look like this:
Codec ORACLE_CODEC = new OracleCodec();String query = "SELECT user_id FROM user_data WHERE user_name = '" + ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("userID"))
+ "' and user_password = '"+ ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("pwd")) +"'";
```
Whatever the input is, it can now be done safely SQL Inject.
For maximum code readability, you can also build your own code OracleEncoder:
```
Encoder oe = new OracleEncoder();String query = "SELECT user\_id FROM user\_data WHERE user\_name = '" + oe.encode( req.getParameter("userID")) + "' and user\_password = '" + oe.encode( req.getParameter("pwd")) +"'";
With this type of solution, you only need to wrap the parameters provided by each user into one ESAPI.encoder().encodeForOracle( )Call, or whatever you name call, and you're done. stay Like Escape wildcard in Clause ============= Should LIKE Keyword allows text scanning search. stay Oracle Middle, underline_Characters match only one character, and＆Symbol%Zero or more occurrences used to match any character. Must be LIKE Escape these characters from clause conditions. For example:
SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/';SELECT name FROM emp WHERE id LIKE '%\%%' ESCAPE '\';
```
MySQL Escape#
========
MySQL Two escape modes are supported:
1. ANSI_QUOTES SQL Mode, and this closed mode, we call it
2. MySQL pattern.
ANSI SQL pattern:'use''(Two single scale) simply encodes all (single scale) characters
MySQL Mode, do the following:
```
NUL (0x00) --> \\0 \[This is a zero, not the letter O\]BS (0x08) --> \\bTAB (0x09) --> \\tLF (0x0a) --> \\nCR (0x0d) --> \\rSUB (0x1a) --> \\Z" (0x22) --> \\"% (0x25) --> \\%' (0x27) --> \\'\ (0x5c) --> \\\_ (0x5f) --> \\_ all other non-alphanumeric characters with ASCII values less than 256 --> \\c where 'c' is the original non-alphanumeric character.
```
SQL Server Escape#
=============
We haven't achieved it yet SQL Server Escape routines, but here are good pointers and links to describe how to prevent SQL On server SQL See here for an article on injection attacks.
DB2 Escape#
======
This information is based on DB2 WebQuery Special characters and Oracle JDBC DB2 Some information in the driver.
About several DB2 Universal Information about differences between drivers.
Hex coded all inputs#
===========
A special case of escape is the process of hexadecimal encoding the entire string received from the user (this can be regarded as escaping each character). Web The application should include user input in SQL It is hexadecimally encoded before in the statement. SQL The statement should take this fact into account and compare the data accordingly.
For example, if we have to find a match sessionID And the user sets the string abc123 As session ID Send, then select The statement will be:
```
SELECT ... FROM session WHERE hex_encode(sessionID) = '616263313233'
```
hex_encode It should be replaced by a specific tool for the database being used. The string 606162313233 is the hexadecimal encoded version of the string received from the user (it is the source of user data) ASCII / UTF-8 The sequence of hexadecimal values of the code).
If an attacker wants to transmit a string containing single quote characters and then try to inject SQL Code, constructed SQL The statement will only be displayed as follows:
```
... WHERE hex_encode ( ... ) = '2720 ... '
```
27 It's single quotation mark ASCII Code (hexadecimal), which is just hexadecimal encoding like any other character in the string. Produced SQL Can only contain numbers and letters a come f，There has never been any special character that it might make SQL Inject.
stay PHP Middle escape SQLi#
============
Use prepared statements and parameterized queries. These are sent and parsed by the database server separately from any parameters SQL sentence. This prevents the attacker from injecting malicious information SQL.
You basically have two choices to achieve this goal:
1. use PDO(For any supported database driver):
```
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(array('name' => $name));foreach ($stmt as $row) { // do something with $row}
```
2. use MySQLi(be used for MySQL):
```
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get\_result();while ($row = $result->fetch\_assoc()) { // do something with $row}
PDO Is a general option. If you want to connect to MySQL For databases other than, you can refer to the second driver specific option (for example, for PostgreSQL，Please use pg\_prepare()and pg\_execute()).
Extra defense
=====
In addition to using one of the four main defenses, we also recommend using all these additional defenses to provide defense in depth. These additional defenses are:
* Minimum authority
* Whitelist input verification
Minimum authority#
=====
To minimize success SQL With the potential damage of injection attacks, you should minimize the permissions assigned to each database account in the environment. Do not assign to your application account DBA Or administrator type access rights. We know it's easy. When you do this, everything is "effective", but it's very dangerous.
Instead of trying to figure out what access you need to take with you, determine what access your application account needs from scratch. Ensure that only accounts that require read access are granted read access to the tables they need to access.
If the account only needs to access some parts of the table, consider creating a view to restrict access to that part of the data and assigning account access rights to the account instead of the underlying table. Rarely, if any, grant create or delete access to database accounts.
If your strategy is to use stored procedures anywhere and do not allow application accounts to directly execute their own queries, limit these accounts to only execute the required stored procedures. Do not grant any permissions directly to tables in the database.
SQL Injection is not the only threat to database data. An attacker can simply change parameter values from one of the legitimate values they present to an unauthorized value, but the application itself may be authorized to access. Unauthorized access to the application will be minimized, even if the attacker does not attempt to grant such access SQL Injection is used as part of its exploit.
When you use it, you should minimize DBMS Permissions of the operating system account running. Don't take root User identity or system operation DBMS!majority DBMS Are out of the box, with a very powerful system account. For example, by default, MySQL stay Windows Run as a system on! Using restricted permissions will DBMS of OS Change the account to a more appropriate account.
Multiple DB user#
=======
Web Application designers should not only avoid Web Use the same owner in the application/Administrator account to connect to the database. different DB Users can use different Web Application.
Typically, you need to access each individual of the database Web Any application can have a specified database user account, Web The application will use this account to connect to the database. In this way, the application designer can have good granularity in access control, so as to reduce privileges as much as possible. Then, each database user can choose to access the content it needs, and write access as needed.
For example, the login page needs to read the user name and password fields of the table, but cannot write any form (no insertion, update or deletion). But, of course, the registration page needs the permission to insert the table; Only when these Web Applications use different DB This restriction can only be enforced when users connect to the database.
see#
===
You can use the SQL Views further increase the granularity of access. It may have additional benefits: for example, suppose that the system needs (possibly due to certain legal requirements) to store the user's password instead of the salted password.
Designers can use views to compensate for this limitation; Revoke all access to the table (except from the owner)/All database users except administrators) and create a view that outputs a hash of the password field instead of the field itself. Any successful theft of database information SQL Injection attacks will be limited to stealing the hash value of the password (possibly even the keyed hash value), because any Web No database user of the application has access to the table itself.