Question

Explain how the use of Prepared Statements prevents SQL injection attacks.

Please give a commented code example, describing the difference between

data base access with and without the use of Prepared Statements (any

programming language may be used for illustration)

Answer 1

SQL injection,is a type of attack where the SQL statements are injected into the back end query,which changes the original purpose of the SQL statements post execution.

In general scenario,the complete SQL query is passed and the input values are read directly.
Prepared statements use question marks (?), which are placeholders for where actual values that needs to be input for the query execution,which at run time is replaced by actual user data.At run-time when the pointer reaches point where the user data needs to be entered,a Pre-Compiled Query is read from Cache and then the placeholders are replaced with user data,where the scope of changing the input data is 0.
  
Consider below java code which aims to select data from table by passing username ,password as "input parameters" to access the Accounts table.

Sample Code:

String query = "SELECT SSN,PARITY_ID FROM AccountsHolder "
+ "WHERE user=" + request.getParameter("username") +
"and password='" + request.getParameter("Password") + "'";
  
try
{
Statement statement = connection.createStatement(); //create the statemet which stores the sql
ResultSet rs = statement.executeQuery(query);
while (rs.next())
{
page.addTableRow(rs.getString("SSN"),
rs.getFloat("PARITY_ID"));
}
}
catch (SQLException e)
{}
The actual user input will be say user=123 and password='tiger'.So the above code upon execution implements this SQL
SELECT SSN,PARITY_ID
FROM AccouontsHolder
WHERE user=123 and password='tiger'

There is chance of SQL injection here,where we can add some conditions which will be always true to make the above username,password filter condition useless.
Ex: 2=2 ;is true always.
The SQL statement then becomes

SELECT SSN,PARITY_ID
FROM AccountsHolder
WHERE user=999 OR '2'='2' and password='junkvalue' OR '2'='2'
-----------------
Now using prepared statement code will be as below,where we use ? instead of passing direct values from user and setting condition as username should only be a integer .
so =1 OR 2=2 will fail here hence the injection will not work ,since OR is not integer

String query = "SELECT SSN,PARITY_ID " +
"FROM AccountsHolder WHERE user = ?
and password = ?";
  
try {
PreparedStatement statement = connection.prepareStatement(query);
statement.setInt(1, request.getParameter("user"));
ResultSet rs = statement.executeQuery();
while (rs.next())
{
page.addTableRow(rs.getString("SSN"),
rs.getFloat("PARITY_ID"));
}
} catch (SQLException e)
{ ... }