How does a PreparedStatement avoid or prevent SQL injection

Question

I know that PreparedStatements avoid prevent SQL Injection  How does it do that  Will the final form query that is constructed using PreparedStatements will be a string or otherwise

User · Answer

Consider two ways of doing the same thing:

PreparedStatement stmt = conn.createStatement("INSERT INTO students VALUES('" + user + "')");
stmt.execute();

Or

PreparedStatement stmt = conn.prepareStatement("INSERT INTO student VALUES(?)");
stmt.setString(1, user);
stmt.execute();

If "user" came from user input and the user input was

Robert'); DROP TABLE students; --

Then in the first instance, you'd be hosed. In the second, you'd be safe and Little Bobby Tables would be registered for your school.

User · Answer

To understand how PreparedStatement prevents SQL Injection  we need to understand phases of SQL Query execution   1  Compilation Phase  2  Execution Phase   Whenever SQL server engine receives a query  it has to pass through below phases       Parsing and Normalization Phase  In this phase  Query is checked for syntax and semantics  It checks whether references table and  columns used in query exist or not  It also has many other tasks to do  but let s not go in detail  Compilation Phase  In this phase  keywords used in query like select  from  where etc are converted into format understandable by machine  This is the phase where query is interpreted and corresponding action to be taken is decided   It also has many other tasks to do  but let s not go in detail  Query Optimization Plan   In this phase  Decision Tree is created for finding the ways in which query can be executed   It finds out the number of ways in which query can be executed and the cost associated with each way   of executing Query    It chooses the best plan for executing a query  Cache  Best plan selected in Query optimization plan is stored in cache  so that whenever next time same query comes in  it doesn t have to pass through Phase 1  Phase 2 and Phase 3 again  When next time query come in  it will be checked directly in Cache and picked up from there to execute  Execution Phase  In this phase  supplied query gets executed and data is returned to user as ResultSet object    Behaviour of PreparedStatement API on above steps   PreparedStatements are not complete SQL queries and contain placeholder s    which at run time are replaced by actual user-provided data  Whenever any PreparedStatment containing placeholders is passed in to SQL Server engine   It passes through below phases    Parsing and Normalization Phase Compilation Phase Query Optimization Plan Cache  Compiled Query with placeholders are stored in Cache         UPDATE user set username   and password   WHERE id      Above query will get parsed  compiled with placeholders as special treatment  optimized and  get Cached  Query at this stage is already compiled and converted in machine understandable format  So we can say that Query stored in cache is Pre-Compiled  and  only placeholders need to be replaced with user-provided data  Now at run-time when user-provided data comes in  Pre-Compiled Query is picked up from Cache and placeholders are replaced with user-provided data       Remember  after place holders are replaced with user data  final query is not  compiled interpreted again and SQL Server engine treats user data as pure data and not a SQL that needs to be parsed or compiled again  that is the beauty of PreparedStatement    If the query doesn t have to go through compilation phase again  then whatever data replaced on the placeholders are treated as pure data and has no meaning to SQL Server engine and it directly executes the query   Note  It is the compilation phase after parsing phase  that understands interprets the query  structure and gives meaningful behavior to it  In case of PreparedStatement  query is compiled only once and cached compiled query is picked up all the time to replace  user data and execute   Due to one time compilation feature of PreparedStatement  it is free of SQL Injection attack   You can get detailed explanation with example here  https   javabypatel blogspot com 2015 09 how-prepared-statement-in-java-prevents-sql-injection html

User · Answer

I guess it will be a string  But the input parameters will be sent to the database  amp  appropriate cast conversions will be applied prior to creating an actual SQL statement   To give you an example  it might try and see if the CAST Conversion works  If it works  it could create a final statement out of it      SELECT   From MyTable WHERE param   CAST  10  DROP TABLE Other  AS varchar 30     Try an example with a SQL statement accepting a numeric parameter  Now  try passing a string variable  with numeric content that is acceptable as numeric parameter   Does it raise any error   Now  try passing a string variable  with content that is not acceptable as numeric parameter   See what happens

User · Answer

Prepared statement is more secure  It will convert a parameter to the specified type   For example stmt setString 1  user   will convert the user parameter to a String   Suppose that the parameter contains a SQL string containing an executable command  using a prepared statement will not allow that   It adds metacharacter  a k a  auto conversion  to that   This makes it is more safe

User · Answer

In Prepared Statements the user is forced to enter data as parameters   If user enters some vulnerable statements like DROP TABLE or SELECT   FROM USERS then data won t be affected as these would be considered as parameters of the SQL statement

User · Answer

PreparedStatement   1  Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches   2  Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters  Note that this requires that you use any of the PreparedStatement setXxx   methods to set the value

User · Answer

PreparedStatement alone does not help you if you are still concatenating Strings  For instance  one rogue attacker can still do the following   call a sleep function so that all your database connections will be busy  therefore making your application unavailable extracting sensitive data from the DB bypassing the user authentication  Not only SQL  but even JPQL or HQL can be compromised if you are not using bind parameters  Bottom line  you should never use string concatenation when building SQL statements  Use a dedicated API for that purpose  like JPA Criteria API

User · Answer

The problem with SQL injection is  that a user input is used as part of the SQL statement  By using prepared statements you can force the user input to be handled as the content of a parameter  and not as a part of the SQL command    But if you don t use the user input as a parameter for your prepared statement but instead build your SQL command by joining strings together  you are still vulnerable to SQL injections even when using prepared statements

User · Answer

SQL injection  when user has the chance to input something that could be part of the sql statement  For example   String query      INSERT INTO students VALUES          user               when user input    Robert      DROP TABLE students         as the input  it causes SQL injection  How prepared statement prevents this   String query      INSERT INTO students VALUES              name                  parameters addValue    name     user       when user input again    Robert      DROP TABLE students          the input string is precompiled on the driver as literal values and I guess it may be casted like   CAST    Robert      DROP TABLE students         AS varchar 30    So at the end  the string will be literally inserted as the name to the table   http   blog linguiming com index php 2018 01 10 why-prepared-statement-avoids-sql-injection

User · Answer

The SQL used in a PreparedStatement is precompiled on the driver  From that point on  the parameters are sent to the driver as literal values and not executable portions of SQL  thus no SQL can be injected using a parameter   Another beneficial side effect of PreparedStatements  precompilation   sending only parameters  is improved performance when running the statement multiple times even with different values for the parameters  assuming that the driver supports PreparedStatements  as the driver does not have to perform SQL parsing and compilation each time the parameters change

[java] How does a PreparedStatement avoid or prevent SQL injection?

Examples related to java

Examples related to sql

Examples related to jdbc

Examples related to prepared-statement

Examples related to sql-injection