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
(Parametrized Query Examples)
m
 
(7 intermediate revisions by 3 users not shown)
Line 1: Line 1:
= DRAFT CHEAT SHEET - WORK IN PROGRESS =
+
Moved: Please see the [[Query Parameterization Cheat Sheet]]
= 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 [https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29 ''parametrized queries'']. The following chart demonstrates, with real-world code samples, how to build parametrized queries in most of the common web languages.
 
 
 
{| class="wikitable nowraplinks"
 
|-
 
! Language - Library
 
! Parametrized Query
 
|-
 
| Java - Standard
 
 
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( );
 
|-
 
| Java - Hibernate
 
|
 
Query safeHQLQuery = session.createQuery("from Inventory where productID=:productid");
 
safeHQLQuery.setParameter("productid", userSuppliedParameter);
 
|-
 
| .NET/C#
 
|
 
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
 
}
 
|-
 
| ASP.NET
 
|
 
string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
 
'''SqlCommand command = new SqlCommand(sql);'''
 
'''command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));'''
 
command.Parameters["@CustomerId"].Value = 1;
 
|-
 
| Ruby - ActiveRecord
 
 
'''# Create'''
 
Project.create!(:name => 'owasp')
 
'''# Read'''
 
Project.all(:conditions => "name = ?", name)
 
Project.all(:conditions => { :name => name })
 
Project.where("name = :name", :name => name)
 
'''# Update'''
 
project.update_attributes(:name => 'owasp')
 
'''# Delete'''
 
Project.delete(:name => 'name')
 
|-
 
| PHP - PDO
 
|
 
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
 
'''$stmt->bindParam(':name', $name);'''
 
'''$stmt->bindParam(':value', $value);'''
 
|-
 
| Cold Fusion
 
|
 
<cfquery name = "getFirst" dataSource = "cfsnippets">
 
    '''SELECT * FROM #strDatabasePrefix#_courses WHERE intCourseID ='''
 
    '''<cfqueryparam value = #intCourseID# CFSQLType = "CF_SQL_INTEGER">'''
 
</cfquery>
 
|-
 
| Perl - DBI
 
|
 
my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )";
 
'''my $sth = $dbh->prepare( $sql );'''
 
'''$sth->execute( $bar, $baz );'''
 
|}
 
 
 
= Related Articles =
 
 
 
{{Cheatsheet_Navigation}}
 
 
 
= Authors and Primary Editors  =
 
 
 
Jim Manico - jim [at] owasp.org
 
 
 
[[Category:Cheatsheets]]
 

Latest revision as of 08:49, 9 March 2012

Moved: Please see the Query Parameterization Cheat Sheet