Does the Preparedstatement Avoid SQL Injection

How can prepared statements protect from SQL injection attacks?

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->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->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.

How does a PreparedStatement avoid or prevent SQL injection?

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.

Does the preparedStatement avoid SQL injection?

Using string concatenation for constructing your query from arbitrary input will not make PreparedStatement safe. Take a look at this example:

preparedStatement = "SELECT * FROM users WHERE name = '" + userName + "';";

If somebody puts

' or '1'='1

as userName, your PreparedStatement will be vulnerable to SQL injection, since that query will be executed on database as

SELECT * FROM users WHERE name = '' OR '1'='1';

So, if you use

preparedStatement = "SELECT * FROM users WHERE name = ?";
preparedStatement.setString(1, userName);

you will be safe.

Some of this code taken from this Wikipedia article.

Is SQL injection possible even on a prepared statement

This query : String query = "SELECT * FROM Users WHERE username=? and password=?"; is safe, because whatever the parameters can be, it will still be executed as a simple select. At most, it will end browsing a whole table.

But prepared statement is just a tool and (bad) programmers may still misuse it.

Let's look at the following query

String query = "SELECT id, " + paramName + " FROM Users WHERE username=? and password=?";

where paramName would be a parameter name. It is only as safe as paramName is, because you use directly a variable to build the string that will be parsed by the database engine. Here PreparedStatement cannot help because JDBC does not allow to parameterize a column name.

So the rule here will be :

avoid such a construct if you can !
if you really need it, double check (regexes, list of allowed strings, etc.) that paramName cannot be anything other than what you expect because that control is the only prevention against SQL injection

Can this prepared statement prevent SQL injection?

Yes it will prevent SQL injection because

Prepared statements uses bound parameters.

Prepared Statements do not combine variables with SQL strings, so it is not possible for an attacker to modify the SQL statement.

Prepared Statements combine the variable with the compiled SQL statement, this means that the SQL and the variables are sent separately and the variables are just interpreted as strings, not part of the SQL statement.

Using prepared statement for Order by to prevent SQL injection java

Do something like this and concatenate it:

List<String> allowedSortableColumns = Arrays.asList(new String[]{"siteid", "technology", "address"})
if(! allowedSortableColumns.contains(sortByColumn)){
   throw new RuntimeException("Cannot sort by: " + sortByColumn);
}
// Continue here and it's safe to concatenate sortByColumn...

You can do sanitization and other stuff, but this should work in your case

How do Prepared Statements prevent SQL injection better than Statements?

You're right that you could do all the sanitation yourself, and thus be safe from injection. But this is more error-prone, and thus less safe. In other words, doing it yourself introduces more chances for bugs that could lead to injection vulnerabilities.

One problem is that escaping rules could vary from DB to DB. For instance, standard SQL only allows string literals in single quotes ('foo'), so your sanitation might only escape those; but MySQL allows string literals in double quotes ("foo"), and if you don't sanitize those as well, you'll have an injection attack if you use MySQL.

If you use PreparedStatement, the implementation for that interface is provided by the appropriate JDBC Driver, and that implementation is responsible for escaping your input. This means that the sanitization code is written by the people who wrote the JDBC driver as a whole, and those people presumably know the ins and outs of the DB's specific escaping rules. They've also most likely tested those escaping rules more thoroughly than you'd test your hand-rolled escaping function.

So, if you write preparedStatement.setString(1, name), the implementation for that method (again, written by the JDBC driver folks for the DB you're using) could be roughly like:

public void setString(int idx, String value) {
    String sanitized = ourPrivateSanitizeMethod(value);
    internalSetString(idx, value);
}

(Keep in mind that the above code is an extremely rough sketch; a lot of JDBC drivers actually handle it quite differently, but the principle is basically the same.)

Another problem is that it could be non-obvious whether myUserInputVar has been sanitized or not. Take the following snippet:

private void updateUser(int name, String id) throws SQLException {
    myStat.executeUpdate("UPDATE user SET name=" + name + " WHERE id=" + id);
}

Is that safe? You don't know, because there's nothing in the code to indicate whether name is sanitized or not. And you can't just re-sanitize "to be on the safe side", because that would change the input (e.g., hello ' world would become hello '' world). On the other hand, a prepared statement of UPDATE user SET name=? WHERE id=? is always safe, because the PreparedStatement's implementation escapes the inputs before it plugs values into the ?.