SQL Injection

Today all Web applications are accessed using the Internet and therefore face the risk of being exposed to manipulation. Most of the Web applications rely on Relational Database Management System (RDBMS) servers, representing a possible vulnerability to SQL injection attacks arising from direct integration of user input into SQL statements without appropriate validation or filtering.

The basis of this vulnerability lies in the creation of SQL commands with character strings. Attackers are successful if they are able to change the semantics of an SQL statement for their benefit or are able to insert their own statements into the application. Entry points can be, for example, input boxes in Web forms or URLs. The results could be unauthorized information access, information disclosure, unauthorized data modification, or data loss.

SQL Manipulation

SQL manipulation can take place as follows:

• Using the operation UNION.
• Changing the WHERE clause.

Examples

Example Code 1:

Original SQL Statement

SELECT fieldlist    FROM table1 WHERE field = 'userinput'. 

Examples for SQL Injection Attack

SELECT fieldlist    FROM table1 WHERE field = 'UNION ALL SELECT other_field             FROM other_table WHERE '='. 

Example Code 2:

Original SQL Statement

SELECT fieldlist    FROM table WHERE field = 'userinput'. 

Examples for SQL Injection Attack

SELECT fieldlist    FROM table WHERE field = 'anything' OR 'x'='x''.  SELECT fieldlist    FROM table WHERE field = 'x' AND email IS NUL; --'. 

Code Injection

Code injection can take place as follows:

• Inserting new database commands into the vulnerable code.
• Appending an SQL server EXECUTE command to the malicious code.

Examples

Example Code 1:

Original SQL Statement

SELECT *    FROM table WHERE name = 'userinput'. 

Examples for SQL Injection Attack

SELECT *    FROM table WHERE name = ' a'; DROP TABLE users;               SELECT *                 FROM  table1               WHERE name = '%''.

Functional Call Injection

Functional call injection is the insertion of various database function calls into vulnerable SQL code.

Several known attack strings listed in the table below may be a part of the SQL injection code to manipulate the original query. Hackers try various input combinations to force SQL statements into an error message. The following list of malicious inputs may or may not give the same results depending on the server. Therefore try all the inputs.

Possible Attack Strings

You need to take into account the different output of possible vulnerable metacharacters in SQL statements. Characters could be displayed as ASCII, HEX, escaped ASCII, and escaped HEX. These four potential notations reveal the complexity of such SQL injection attacks.

Possible Characters to be used in SQL Code Injection

Examples of Combinations of ASCII and HEX-Characters Used Within Malicious Code

1. Open SQL/JDBC and Open SQL/SQLJ provide some implicit protection against SQL code injection as follows:
   • Since all statements are prepared, it is not possible to insert malicious code snippets using host variables, as, for example, the comparison values of a WHERE clause.
   • The SQL statements SELECT, MODIFY, UPDATE, INSERT, and DELETE may all have dynamic clauses. However:
     • The leading keyword of a clause has to be static.
     • No SQL statement can be executed within a clause of another statement completely dynamically.
     • Subqueries can contain dynamic clauses but the leading SELECT keyword is always static.
2. JDBC: with regard to vulnerability to SQL injection attacks you should keep in mind:
   • Instead of using java.sql.Statement which is vulnerable to SQL injection as shown below:

     String wcl = request.getParameter("para"); if (  ! (wcl == null) ) {      wcl = "where key1 = '" + wcl + "'"; } else {      wcl = "where key1 = ''"; } try {   Context ctx = new InitialContext();   DataSource ds = (DataSource)   ctx.lookup("java:comp/env/TMP_MYTABLE");   Connection conn = ds.getConnection();   Statement stmt = conn.createStatement();   ResultSet rs =      stmt.executeQuery(         "select key1,nonkey from TMP_MYTABLE " + wcl);   while (rs.next())   {       … 
     
     Write the results to HTML-output   } }

   • Wrap SQL strings with instances of java.sql.PreparedStatement, which produces safer coding:

     String wcl = request.getParameter("para"); // method to validate input stream -> validateInput if ( validateIput(wcl) )   if (   (wcl == null) )  {     wcl = "";   }   try {     Context ctx = new InitialContext();     DataSource ds =      (DataSource)ctx.lookup("java:comp/env/TMP_MYTABLE");     Connection conn = ds.getConnection();     String stmt =         "select key1,nonkey from TMP_MYTABLE  where key1 = ?";     PreparedStatement ps = conn.prepareStatement(stmt);     ps.setString(1,wcl);     ResultSet rs = ps.executeQuery();     while (rs.next()) {      … write output to HTML code     }   } } else {   … write security alert … }

   • Filter user input while retrieving user information for your SQL statement.

As mentioned above, the information in Relational Database Management Systems is stored and retrieved with SQL statements. Therefore the following general rules may be helpful to circumvent SQL injection attacks:

• Define a codepage (such as charset = ISO-8859-1 ) to clearly decide which characters are problematic.
• Filter user input while retrieving user information for your SQL statement.
• Filter your data with the following regular expression for numbers and letters.
  • s/[^0-9a-zA-z]//g
• If the user is allowed to submit an email address, allow only "@", "_", "." and "-".
• Enclose all user input in quotation marks, even if it is numeric.
• Restrict the rights of the Web application user.
• Configure error reporting.
  • Restrict error reporting (by server-side and by application) so that internal system information cannot be shown to outside users. If the full query is shown, pointing to the syntax error involved, this aids hackers in mounting Cross-Site Scripting (XSS) attacks.

If the developer allows unfiltered user input values to be taken for a SELECT statement, any attack may be possible to read almost the whole database.