This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

Difference between revisions of "Abridged SQL Injection Prevention Cheat Sheet"

From OWASP
Jump to: navigation, search
m (Parametrized Query Examples)
m (Parametrized Query Examples)
Line 15: Line 15:
 
| Java - Standard
 
| Java - Standard
 
|   
 
|   
String custname = request.getParameter("customerName");  
+
String custname = request.getParameter("customerName");  
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";   
+
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";   
'''PreparedStatement pstmt = connection.prepareStatement( query );'''
+
'''PreparedStatement pstmt = connection.prepareStatement( query );'''
'''pstmt.setString( 1, custname); '''
+
'''pstmt.setString( 1, custname); '''
ResultSet results = pstmt.executeQuery( );
+
ResultSet results = pstmt.executeQuery( );
 
|-
 
|-
 
| Java - Hibernate
 
| Java - Hibernate
 
|
 
|
  Query safeHQLQuery = session.createQuery("from Inventory where productID=:productid");
+
Query safeHQLQuery = session.createQuery("from Inventory where productID=:productid");
  safeHQLQuery.setParameter("productid", userSuppliedParameter);
+
safeHQLQuery.setParameter("productid", userSuppliedParameter);
 
|-
 
|-
 
| .NET/C#
 
| .NET/C#
 
|
 
|
  String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
+
String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
  try {
+
try {
  OleDbCommand command = new OleDbCommand(query, connection);
+
    OleDbCommand command = new OleDbCommand(query, connection);
  '''command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));'''
+
    '''command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));'''
  OleDbDataReader reader = command.ExecuteReader();
+
    OleDbDataReader reader = command.ExecuteReader();
  // …
+
    // …
  } catch (OleDbException se) {
+
} catch (OleDbException se) {
  // error handling
+
    // error handling
  }  
+
}  
 
|-
 
|-
 
| ASP.NET
 
| ASP.NET
 
|
 
|
  string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
+
string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
  '''SqlCommand command = new SqlCommand(sql);'''
+
'''SqlCommand command = new SqlCommand(sql);'''
  '''command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));'''
+
'''command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));'''
  command.Parameters["@CustomerId"].Value = 1;
+
command.Parameters["@CustomerId"].Value = 1;
 
|-
 
|-
 
| Ruby
 
| Ruby
 
|  
 
|  
  '''Project.all(:conditions => "name = ?", name)'''
+
'''Project.all(:conditions => "name = ?", name)'''
  '''Project.all(:conditions => { :name => name })'''
+
'''Project.all(:conditions => { :name => name })'''
  '''Project.all(:conditions => "name = '#{params[:name]}'")'''
+
'''Project.all(:conditions => "name = '#{params[:name]}'")'''
 
|-
 
|-
 
| PHP - PDO
 
| PHP - PDO
 
|  
 
|  
  $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
+
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
  '''$stmt->bindParam(':name', $name);'''
+
'''$stmt->bindParam(':name', $name);'''
  '''$stmt->bindParam(':value', $value);'''
+
'''$stmt->bindParam(':value', $value);'''
 
|-
 
|-
 
| Cold Fusion
 
| Cold Fusion
 
|  
 
|  
  <cfquery name = "getFirst" dataSource = "cfsnippets">
+
<cfquery name = "getFirst" dataSource = "cfsnippets">
      '''SELECT * FROM #strDatabasePrefix#_courses WHERE intCourseID ='''
+
    '''SELECT * FROM #strDatabasePrefix#_courses WHERE intCourseID ='''
      '''<cfqueryparam value = #intCourseID# CFSQLType = "CF_SQL_INTEGER">'''
+
    '''<cfqueryparam value = #intCourseID# CFSQLType = "CF_SQL_INTEGER">'''
  </cfquery>
+
</cfquery>
 
|-
 
|-
 
| Perl - DBI
 
| Perl - DBI
 
|  
 
|  
  my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )";
+
my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )";
  '''my $sth = $dbh->prepare( $sql );'''
+
'''my $sth = $dbh->prepare( $sql );'''
  '''$sth->execute( $bar, $baz );'''
+
'''$sth->execute( $bar, $baz );'''
 
|}
 
|}
  

Revision as of 02:24, 18 November 2011

DRAFT CHEAT SHEET - WORK IN PROGRESS

Introduction

SQL Injection is one of the most damaging web vulnerabilities. It represents a serious threat because SQL Injection allows evil attacker code to change the structure of a web application's SQL statement in a way that can steal data, modify data, or facilitate command injection. This cheat sheet is a derivative work of the SQL Injection Prevention Cheat Sheet.

Parametrized Query Examples

SQL Injection is best prevented through the use of parametrized queries. The following chart demonstrates, with real-world code samples, how to build parametrized queries in most of the common web languages.

Related Articles

OWASP Cheat Sheets Project Homepage


Authors and Primary Editors

Jim Manico - jim [at] owasp.org