How does PreparedStatement prevent or prevent SQL injection?

Question

I know that PreparedStatements avoids / prevents SQL injection. How does it do it? Will the final form request that is built using PreparedStatements be a string or otherwise?

Answer 1

The problem with SQL injection is that user input is used as part of an SQL statement. Using prepared statements, you can force user input to be processed as the contents of a parameter (and not as part of an SQL command).

But if you do not use user input as a parameter for your prepared statement, but instead create your own SQL command by combining the rows together, you are still vulnerable to SQL injection even when using prepared statements.

Answer 2

Consider two ways to do 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 the "user" came from user input and user input was

 Robert'); DROP TABLE students; -- 

Then, in the first case, you will be closed. Secondly, you would be safe, and Little Bobby Tables would be registered for your school.

Answer 3

To understand how PreparedStatement prevents SQL injection, we need to understand the steps involved in executing an SQL query.

1. The compilation phase. 2. The execution phase.

Whenever a SQL Server engine receives a query, it must go through the phases below,

1. Parsing and Normalization Phase: At this point, Query is checked for syntax and semantics. It checks if the link tables and columns used in the query exist or not. He also has many other tasks, but he should not stop in detail.

2. Compilation phase: at this stage, the keywords used in the query such as select, from, where, etc., are converted to a format that the machine understands. This is the stage when the request is interpreted and the corresponding action is taken. He also has many other tasks, but he should not stop in detail.

3. Query optimization plan: at this stage, a decision tree is created to find ways to fulfill the request. It determines the number of ways to fulfill the request and the costs associated with each method of performing the request. He chooses the best plan to fulfill the request.

4. Cache: the best plan selected in terms of query optimization is stored in the cache, so when the next request arrives the next time, it should not go through phases 1, phase 2 and phase 3 again. When the next time request arrives, it will be checked directly in the cache and selected from there to execute.

5. Execution phase: at this stage, the request is completed, and the data is returned to the user as a ResultSet object.

PreparedStatement API Behavior in the Steps Above

1. PreparedStatements are not complete SQL queries and contain placeholders (s) that are replaced at run time by the actual data provided by the user.

2. Whenever any PreparedStatment containing placeholders is passed to the SQL Server engine, it goes through the phases below

   1. Analysis and Normalization Phase
   2. Compilation phase
   3. Query optimizer
   4. Cache (a compiled request with placeholders is stored in the cache.)

Username UPDATE =? and password =? WHERE id =?

3. The above request will be parsed, compiled with placeholders as a special treatment, optimized and received by Cached. The request at this stage has already been compiled and converted in a format understandable for the machine. Thus, we can say that the Query stored in the cache is precompiled, and only placeholders should be replaced with data provided by the user.

4. Now at runtime, when the data provided by the user is provided, the Pre-Compiled Query is selected from the cache, and the replacement ones are replaced by data provided by the user.

(Remember that after the place owners are replaced with user data, the final query has not yet been compiled / interpreted, and the SQL Server engine treats the user data as pure data, not SQL, which needs to be analyzed or compiled again, that is, the beauty of PreparedStatement.)

If the query does not have to go through the compilation phase again, then any data replaced with placeholders is treated as pure data and has no meaning for the SQL Server mechanism, and it directly executes the query.

Note. This is the compilation stage after the analysis phase, which understands / interprets the structure of the request and gives meaningful behavior. In the case of PreparedStatement, the request is compiled only once, and the cached compiled request is always selected to replace user data and execute.

Due to a single compilation function in PreparedStatement, it does not contain a SQL Injection attack.

A detailed explanation can be found here: http://javabypatel.blogspot.in/2015/09/how-prepared-statement-in-java-prevents-sql-injection.html

Answer 4

The SQL used in PreparedStatement is precompiled in the driver. From this moment, the parameters are sent to the driver as literal values, not the executable parts of SQL; therefore, SQL cannot be entered using the parameter. Another useful side effect of PreparedStatements (pre-compilation + sending only parameters) is increased performance when executing the statement several times even with different parameter values ​​(provided that the driver supports PreparedStatements), since the driver does not need to parse and compile SQL every time the parameters are changed.

Answer 5

I Will guess the line. But the input parameters will be sent to the database and the corresponding corresponding casts / transforms will be applied before the actual SQL statement is created.

To give you an example, he can try and see if CAST / Conversion works.
If it works, it can create a final conclusion from it.

  SELECT * From MyTable WHERE param = CAST('10; DROP TABLE Other' AS varchar(30)) 

Try the example with an SQL statement that accepts a numeric parameter.
Now try passing a string variable (with numeric content valid as a numeric parameter). Does an error occur?

Now try passing a string variable (with content that is not acceptable as a numeric parameter). See what happens?

Answer 6

A trained operator is safer. It converts the parameter to the specified type.

For example, stmt.setString(1, user); converts the user parameter to a string.

Suppose a parameter contains an SQL string containing an executable command : using a prepared statement will not allow this.

He adds a metacharacter (aka transformation) to this.

This makes it safer.

Answer 7

As explained in this post , PreparedStatement will not help you if you are still concatenating Strings.

For example, one of the malefactors may do the following:

• call the sleep function so that all database connections are busy, so your application is not available
• extracting confidential data from the database
• user authentication bypass

Not only SQL, but even JPQL or HQL can be compromised if you do not use the binding parameters.

On the bottom line, you should never use string concatenation when creating SQL statements. To do this, use the special API:

• JPA API
• jOOQ

Answer 8

SQL injection: when the user is able to enter what can be part of the sql statement

For example:

String query = "INSERT INTO students VALUES ('" + user + "')"

when the user enters "Robert", the students of DROP TABLE, - "as an input, it causes an SQL injection

How does a prepared statement prevent this?

String query = "INSERT INTO students VALUES ('" + ": name" + "')"

parameters.addValue ("name", user);

=> when the user enters "Robert" again, the students of DROP TABLE, - "the input string is precompiled in the driver as literals, and I assume that it can be executed as:

CAST ('Robert); Students DROP TABLE; - 'AS varchar (30))

Therefore, at the end, the row will be literally inserted as a name in the table.

http://blog.linguiming.com/index.php/2018/01/10/why-prepared-statement-avoids-sql-injection/

Answer 9

PreparedStatement:

1) Pre-compiling and caching the database of the SQL statement leads to faster execution and the possibility of reusing the same SQL statement in packages.

2) Automatically prevent SQL injection attacks by integrated escaping of quotes and other special characters. Note that you must use any of the PreparedStatement setXxx () methods to set the value.