How can prepared statements protect from SQL injection attacks

Question

How do prepared statements help us prevent SQL injection attacks  Wikipedia says   Prepared statements are resilient against SQL injection  because parameter values  which are transmitted later using a different protocol  need not be correctly escaped  If the original statement template is not derived from external input  SQL injection cannot occur   I cannot see the reason very well  What would be a simple explanation in an easy English and some examples

User · Answer

I read through the answers and still felt the need to stress the key point which illuminates the essence of Prepared Statements. Consider two ways to query one's database where user input is involved:

Naive Approach

One concatenates user input with some partial SQL string to generate a SQL statement. In this case the user can embed malicious SQL commands, which will then be sent to the database for execution.

String SQLString = "SELECT * FROM CUSTOMERS WHERE NAME='"+userInput+"'"

For example, malicious user input can lead to SQLString being equal to "SELECT * FROM CUSTOMERS WHERE NAME='James';DROP TABLE CUSTOMERS;'

Due to the malicious user, SQLString contains 2 statements, where the 2nd one ("DROP TABLE CUSTOMERS") will cause harm.

Prepared Statements

In this case, due to the separation of the query & data, the user input is never treated as a SQL statement, and thus is never executed. It is for this reason, that any malicious SQL code injected would cause no harm. So the "DROP TABLE CUSTOMERS" would never be executed in the case above.

In a nutshell, with prepared statements malicious code introduced via user input will not be executed!

User · Answer

Basically  with prepared statements the data coming in from a potential hacker is treated as data - and there s no way it can be intermixed with your application SQL and or be interpreted as SQL  which can happen when data passed in is placed directly into your application SQL    This is because prepared statements  prepare  the SQL query first to find an efficient query plan  and send the actual values that presumably come in from a form later - at that time the query is actually executed     More great info here   Prepared statements and SQL Injection

User · Answer

Here is SQL for setting up an example   CREATE TABLE employee name varchar  paymentType varchar  amount bigint    INSERT INTO employee VALUES  Aaron    salary   100   INSERT INTO employee VALUES  Aaron    bonus   50   INSERT INTO employee VALUES  Bob    salary   50   INSERT INTO employee VALUES  Bob    bonus   0     The Inject class is vulnerable to SQL injection  The query is dynamically pasted together with user input  The intent of the query was to show information about Bob  Either salary or bonus  based on user input  But the malicious user manipulates the input corrupting the query by tacking on the equivalent of an  or true  to the where clause so that everything is returned  including the information about Aaron which was supposed to be hidden   import java sql     public class Inject        public static void main String   args  throws SQLException            String url    jdbc postgresql   localhost postgres user user amp password pwd           Connection conn   DriverManager getConnection url            Statement stmt   conn createStatement            String sql    SELECT paymentType  amount FROM employee WHERE name    bob  AND paymentType      args 0                 System out println sql           ResultSet rs   stmt executeQuery sql            while  rs next                  System out println rs getString  paymentType           rs getLong  amount                         Running this  the first case is with normal usage  and the second with the malicious injection   c  temp gt java Inject salary SELECT paymentType  amount FROM employee WHERE name    bob  AND paymentType  salary  salary 50  c  temp gt java Inject  salary  OR  a    b  SELECT paymentType  amount FROM employee WHERE name    bob  AND paymentType  salary  OR  a    b  salary 100 bonus 50 salary 50 bonus 0   You should not build your SQL statements with string concatenation of user input  Not only is it vulnerable to injection  but it has caching implications on the server as well  the statement changes  so less likely to get a SQL statement cache hit whereas the bind example is always running the same statement    Here is an example of Binding to avoid this kind of injection   import java sql     public class Bind        public static void main String   args  throws SQLException            String url    jdbc postgresql   localhost postgres user postgres amp password postgres           Connection conn   DriverManager getConnection url            String sql    SELECT paymentType  amount FROM employee WHERE name    bob  AND paymentType             System out println sql            PreparedStatement stmt   conn prepareStatement sql           stmt setString 1  args 0             ResultSet rs   stmt executeQuery             while  rs next                  System out println rs getString  paymentType           rs getLong  amount                         Running this with the same input as the previous example shows the malicious code does not work because there is no paymentType matching that string   c  temp gt java Bind salary SELECT paymentType  amount FROM employee WHERE name    bob  AND paymentType   salary 50  c  temp gt java Bind  salary  OR  a    b  SELECT paymentType  amount FROM employee WHERE name    bob  AND paymentType

User · Answer

When you create and send a prepared statement to the DBMS  it s stored as the SQL query for execution   You later bind your data to the query such that the DBMS uses that data as the query parameters for execution  parameterization    The DBMS doesn t use the data you bind as a supplemental to the already compiled SQL query  it s simply the data   This means it s fundamentally impossible to perform SQL injection using prepared statements   The very nature of prepared statements and their relationship with the DBMS prevents this

User · Answer

In SQL nbsp Server  using a prepared statement is definitely injection-proof because the input parameters don t form the query  It means that the executed query is not a dynamic query  Example of an SQL injection vulnerable statement   string sqlquery    select   from table where username      inputusername     and password      pass          Now if the value in the inoutusername variable is something like a  or 1 1 --  this query now becomes   select   from table where username  a  or 1 1 -- and password asda   And the rest is commented after --  so it never gets executed and bypassed as using the prepared statement example as below   Sqlcommand command   new sqlcommand  select   from table where username    userinput and password  pass    command Parameters Add new SqlParameter   userinput   100    command Parameters Add new SqlParameter   pass   100    command prepare      So in effect you cannot send another parameter in  thus avoiding SQL injection

User · Answer

Root Cause  1 - The Delimiter Problem  Sql injection is possible because we use quotation marks to delimit strings and also to be parts of strings  making it impossible to interpret them sometimes   If we had delimiters that could not be used in string data  sql injection never would have happened  Solving the delimiter problem eliminates the sql injection problem  Structure queries do that   Root Cause  2 - Human Nature  People are Crafty and Some Crafty People Are Malicious And All People Make Mistakes  The other root cause of sql injection is human nature   People  including programmers  make mistakes   When you make a mistake on a structured query  it does not make your system vulnerable to sql injection   If you are not using structured queries  mistakes can generate sql injection vulnerability   How Structured Queries Resolve the Root Causes of SQL Injection  Structured Queries Solve The Delimiter Problem  by by putting sql commands in one statement and putting the  data  in a separate programming statement   Programming statements create the separation needed   Structured queries help prevent human error from creating critical security holes  With regard to humans making mistakes  sql injection cannot happen when structure queries are used  There are ways of preventing sql injection that don t involve structured queries  but normal human error in that approaches usually leads to at least some exposure to sql injection   Structured Queries are fail safe from sql injection   You can make all the mistakes in the world  almost  with structured queries  same as any other programming  but none that you can make can be turned into a ssstem taken over by sql injection   That is why people like to say this is the right way to prevent sql injection   So  there you have it  the causes of sql injection and the nature structured queries that makes them impossible when they are used

User · Answer

The idea is very simple - the query and the data are sent to the database server separately  That s all   The root of the SQL injection problem is in the mixing of the code and the data      In fact  our SQL query is a legitimate program   And we are creating such a program dynamically  adding some data on the fly  Thus  the data may interfere with the program code and even alter it  as every SQL injection example shows it  all examples in PHP Mysql     expected data   1   query    SELECT   FROM users where id  expected data     will produce a regular query   SELECT   FROM users where id 1   while this code   spoiled data    1  DROP TABLE users    query           SELECT   FROM users where id  spoiled data     will produce a malicious sequence  SELECT   FROM users where id 1  DROP TABLE users    It works because we are adding the data directly to the program body and it becomes a part of the program  so the data may alter the program  and depending on the data passed  we will either have a regular output or a table users deleted   While in case of prepared statements we don t alter our program  it remains intact That s the point    We are sending a program to the server first   db- gt prepare  SELECT   FROM users where id        where the data is substituted by some variable called a parameter or a placeholder   Note that exactly the same query is sent to the server  without any data in it  And then we re sending the data with the second request  essentially separated from the query itself    db- gt execute  data     so it can t alter our program and do any harm  Quite simple - isn t it    The only thing I have to add that always omitted in the every manual   Prepared statements can protect only data literals  but cannot be used with any other query part  So  once we have to add  say  a dynamical identifier - a field name  for example - prepared statements can t help us  I ve explained the matter recently  so I won t repeat myself

User · Answer

ResultSet rs   statement executeQuery  select   from foo where value       httpRequest getParameter  filter      Let   s assume you have that in a Servlet you right  If a malevolent person passed a bad value for  filter  you might hack your database

User · Answer

The key phrase is  need not be correctly escaped  That means that you don t to worry about people trying to throw in dashes  apostrophes  quotes  etc     It is all handled for you

[sql] How can prepared statements protect from SQL injection attacks?

Examples related to sql

Examples related to security

Examples related to sql-injection

Examples related to prepared-statement