Reviewing Code for SQL Injection - Revision history

Thomas Herlea: Added navigation to facilitate sequential reading online

2010-09-09T16:15:11Z

Added navigation to facilitate sequential reading online

MediaWiki spam cleanup: Reverting to last version not containing links to www.textcnagetacv.com

2009-05-27T18:30:23Z

Reverting to last version not containing links to www.textcnagetacv.com

MediaWiki spam cleanup: Reverting to last version not containing links to s1.shard.jp

2009-05-27T16:00:16Z

Reverting to last version not containing links to s1.shard.jp

Deleted user at 12:08, 26 May 2009

2009-05-26T12:08:38Z

Deleted user at 18:52, 22 May 2009

2009-05-22T18:52:38Z

KirstenS: /* .NET */

2009-02-11T01:10:12Z

‎.NET

KirstenS: /* Classic ASP */

2009-01-13T02:58:50Z

‎Classic ASP

KirstenS: /* .NET */

2009-01-13T02:57:27Z

‎.NET

KirstenS: /* SQL Injection Example: */

2009-01-13T02:56:50Z

‎SQL Injection Example:

KirstenS: /* Best practices when dealing with DB’s */

2009-01-13T02:55:55Z

‎Best practices when dealing with DB’s

@@ Line 1: / Line 1: @@
-[[OWASP Code Review Guide Table of Contents]]__TOC__
+{{LinkBar
 == Overview ==
@@ Line 185: / Line 190: @@
 [[Category:OWASP Code Review Project]]

@@ Line 1: / Line 1: @@
 [[OWASP Code Review Guide Table of Contents]]__TOC__
@@ Line 25: / Line 24: @@
 A secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement and/or to use parameterized stored procedures. Parameterized stored procedures are compiled before user input is added, making it impossible for a hacker to modify the actual SQL statement.
-The account used to make the database connection must have âLeast privilege.â If the application only requires read access then the account must be given read access only.
+The account used to make the database connection must have “Least privilege.” If the application only requires read access then the account must be given read access only.
 Avoid disclosing error information: Weak error handling is a great way for an attacker to profile SQL injection attacks. Uncaught SQL errors normally give too much information to the user and contain things like table names and procedure names.
@@ Line 118: / Line 117: @@
 </pre>
-'''Stored procedures donât always protect against SQL injection:'''
+'''Stored procedures don’t always protect against SQL injection:'''
 <pre>
@@ Line 131: / Line 130: @@
 </pre>
-The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in MicrosoftÂ® SQL Serverâ¢
+The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in Microsoft® SQL Server™
 Lets pass it.
@@ Line 141: / Line 140: @@
 </pre>
-Guess what happens? So we must be careful of not falling into the âWeâre secure, we are using stored proceduresâ trap!
+Guess what happens? So we must be careful of not falling into the “We’re secure, we are using stored procedures” trap!
 == Classic ASP ==

@@ Line 1: / Line 1: @@
 http://www.textcnagetacv.com
 [[OWASP Code Review Guide Table of Contents]]__TOC__
@@ Line 68: / Line 25: @@
 A secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement and/or to use parameterized stored procedures. Parameterized stored procedures are compiled before user input is added, making it impossible for a hacker to modify the actual SQL statement.
-The account used to make the database connection must have Ã¢ÂÂLeast privilege.Ã¢ÂÂ If the application only requires read access then the account must be given read access only.
+The account used to make the database connection must have âLeast privilege.â If the application only requires read access then the account must be given read access only.
 Avoid disclosing error information: Weak error handling is a great way for an attacker to profile SQL injection attacks. Uncaught SQL errors normally give too much information to the user and contain things like table names and procedure names.
@@ Line 161: / Line 118: @@
 </pre>
-'''Stored procedures donÃ¢ÂÂt always protect against SQL injection:'''
+'''Stored procedures donât always protect against SQL injection:'''
 <pre>
@@ Line 174: / Line 131: @@
 </pre>
-The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in MicrosoftÃÂ® SQL ServerÃ¢ÂÂ¢
+The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in MicrosoftÂ® SQL Serverâ¢
 Lets pass it.
@@ Line 184: / Line 141: @@
 </pre>
-Guess what happens? So we must be careful of not falling into the Ã¢ÂÂWeÃ¢ÂÂre secure, we are using stored proceduresÃ¢ÂÂ trap!
+Guess what happens? So we must be careful of not falling into the âWeâre secure, we are using stored proceduresâ trap!
 == Classic ASP ==

@@ Line 1: / Line 1: @@
 http://www.textcnagetacv.com
 [[OWASP Code Review Guide Table of Contents]]__TOC__
@@ Line 25: / Line 68: @@
 A secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement and/or to use parameterized stored procedures. Parameterized stored procedures are compiled before user input is added, making it impossible for a hacker to modify the actual SQL statement.
-The account used to make the database connection must have âLeast privilege.â If the application only requires read access then the account must be given read access only.
+The account used to make the database connection must have Ã¢ÂÂLeast privilege.Ã¢ÂÂ If the application only requires read access then the account must be given read access only.
 Avoid disclosing error information: Weak error handling is a great way for an attacker to profile SQL injection attacks. Uncaught SQL errors normally give too much information to the user and contain things like table names and procedure names.
@@ Line 118: / Line 161: @@
 </pre>
-'''Stored procedures donât always protect against SQL injection:'''
+'''Stored procedures donÃ¢ÂÂt always protect against SQL injection:'''
 <pre>
@@ Line 131: / Line 174: @@
 </pre>
-The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in MicrosoftÂ® SQL Serverâ¢
+The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in MicrosoftÃÂ® SQL ServerÃ¢ÂÂ¢
 Lets pass it.
@@ Line 141: / Line 184: @@
 </pre>
-Guess what happens? So we must be careful of not falling into the âWeâre secure, we are using stored proceduresâ trap!
+Guess what happens? So we must be careful of not falling into the Ã¢ÂÂWeÃ¢ÂÂre secure, we are using stored proceduresÃ¢ÂÂ trap!
 == Classic ASP ==

@@ Line 1: / Line 1: @@
 [[OWASP Code Review Guide Table of Contents]]__TOC__
@@ Line 24: / Line 25: @@
 A secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement and/or to use parameterized stored procedures. Parameterized stored procedures are compiled before user input is added, making it impossible for a hacker to modify the actual SQL statement.
-The account used to make the database connection must have “Least privilege.” If the application only requires read access then the account must be given read access only.
+The account used to make the database connection must have âLeast privilege.â If the application only requires read access then the account must be given read access only.
 Avoid disclosing error information: Weak error handling is a great way for an attacker to profile SQL injection attacks. Uncaught SQL errors normally give too much information to the user and contain things like table names and procedure names.
@@ Line 117: / Line 118: @@
 </pre>
-'''Stored procedures don’t always protect against SQL injection:'''
+'''Stored procedures donât always protect against SQL injection:'''
 <pre>
@@ Line 130: / Line 131: @@
 </pre>
-The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in Microsoft® SQL Server™
+The above procedure shall execute any SQL you pass to it. The directive sp_executesql is a system stored procedure in MicrosoftÂ® SQL Serverâ¢
 Lets pass it.
@@ Line 140: / Line 141: @@
 </pre>
-Guess what happens? So we must be careful of not falling into the “We’re secure, we are using stored procedures” trap!
+Guess what happens? So we must be careful of not falling into the âWeâre secure, we are using stored proceduresâ trap!
 == Classic ASP ==

@@ Line 89: / Line 89: @@
 == .NET ==
-Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code, and therefore the payload can not be injected. Using a parameters collection lets you enforce type and length checks. Values outside of the range trigger an exception. Make sure you handle the exception correctly. Example of the SqlParameterCollection:
+Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code, and therefore the payload cannot be injected. Using a parameters collection lets you enforce type and length checks. Values outside of the range trigger an exception. Make sure you handle the exception correctly. Example of the SqlParameterCollection:
 <pre>

@@ Line 143: / Line 143: @@
 == Classic ASP ==
-For this technology you can use parameterized queries to avoid Sql injection attacks. Here is a good example:
+For this technology you can use parameterized queries to avoid SQL injection attacks. Here is a good example:
 <pre>
 <%
@@ Line 176: / Line 176: @@
 </pre>
-Notice that this is Sql Server Specific code if you would use a ODBC/Jet connection to another DB which ISAM supports parameterized queries you should change your query to the following:
+Notice that this is SQL Server Specific code. If you would use a ODBC/Jet connection to another DB which ISAM supports parameterized queries ,you should change your query to the following:
   cmd.commandtext="Select * from DataTable where Id = ?"

@@ Line 79: / Line 79: @@
 </pre>
-When SQL statements are dynamically created as software executes, there is an opportunity for a
+When SQL statements are dynamically created as software executes, there is an opportunity for a security breach as the input data can truncate or malform or even expand the original SQL query!
-Firstly the request.getParameter retrieves the data for the SQL query directly from the HTTP request without any data validation (Min/Max length, Permitted characters, Malicious characters). This error gives rise to the ability to input SQL as the payload and alter the functionality in the statement.
+Firstly, the request.getParameter retrieves the data for the SQL query directly from the HTTP request without any data validation (Min/Max length, Permitted characters, Malicious characters). This error gives rise to the ability to input SQL as the payload and alter the functionality in the statement.
 String sel = "SELECT User_id, Username FROM USERS WHERE Username = '" Username + "' AND Password = '" + Password + "'";

@@ Line 28: / Line 28: @@
 Avoid disclosing error information: Weak error handling is a great way for an attacker to profile SQL injection attacks. Uncaught SQL errors normally give too much information to the user and contain things like table names and procedure names.
-== Best practices when dealing with DB’s ==
+== Best Practices when Dealing with Databases ==
 Use Database stored procedures, but even stored procedures can be vulnerable. Use parameterized queries instead of dynamic SQL statements. Data validate all external input: Ensure that all SQL statements recognize user inputs as variables, and that statements are precompiled before the actual inputs are substituted for the variables in Java.
 == SQL Injection Example: ==