Asked  7 Months ago    Answers:  5   Viewed   47 times

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?

 Answers

46

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.

Tuesday, June 1, 2021
 
altermativ
answered 7 Months ago
15

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.

Wednesday, June 9, 2021
 
jakubos
answered 6 Months ago
22

For two (2) different SQL queries you will need two (2) different PreparedStatement objects and each one will have its own batch, but you can simply execute each batch when you want to send the queries to the server:

try (
        PreparedStatement thisPs = conn.prepareStatement("INSERT INTO thisTable (thisId, thisText) VALUES (?,?)");
        PreparedStatement thatPs = conn.prepareStatement("INSERT INTO thatTable (thatId, thatText) VALUES (?,?)")) {

    thisPs.setInt(1, 1);
    thisPs.setString(2, "thisText1");
    thisPs.addBatch();

    thatPs.setInt(1, 1);
    thatPs.setString(2, "thatText1");
    thatPs.addBatch();

    thisPs.setInt(1, 2);
    thisPs.setString(2, "thisText2");
    thisPs.addBatch();

    thatPs.setInt(1, 2);
    thatPs.setString(2, "thatText2");
    thatPs.addBatch();

    thisPs.executeBatch();
    thatPs.executeBatch();
}

Also, be aware of terminology. Talking about a "batch transaction" is somewhat ambiguous:

  • addBatch and executeBatch are part of the mechanism to send multiple statements to the server as a single batch (transmission). This affects the way the statements are sent (transmitted) to the database server.

  • A database transaction is the mechanism whereby a number of statements will be processed as a complete group, i.e., either the whole group will be processed ("committed") or the whole group will be discarded ("rolled back"). The Connection#setAutoCommit(), Connection#commit(), and Connection#rollback() methods control this behaviour. This affects the way the statements are executed by the database server.

Monday, August 16, 2021
 
Ripon Al Wasim
answered 4 Months ago
86

In my book, I would always recommend closing resources that have been opened to avoid possible leaks.

A slightly more modern way would be to use try-with-resources:

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "password")) {

    try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO PUBLISHER (CODE, PUBLISHER_NAME) VALUES (?, ?)")) {
        stmt.setString(1, book.getPublisher().getCode());   
        stmt.setString(2, book.getPublisher().getName());           
        stmt.executeUpdate();
    }
    // stmt is auto closed here, even if SQLException is thrown

    try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO BOOK (ISBN, BOOK_NAME, PUBLISHER_CODE) VALUES (?, ?, ?)");
        stmt.setString(1, book.getIsbn());  
        stmt.setString(2, book.getName());
        stmt.setString(3, book.getPublisher().getCode());
        stmt.executeUpdate();
    }
    // stmt is auto closed here, even if SQLException is thrown
}
// connection is auto closed here, even if SQLException is thrown
Tuesday, October 12, 2021
 
Muhd
answered 2 Months ago
90

(There's a bug in the 2nd line; the string isn't terminated. Add a "); to the end, and you should be ok. It's on the page you linked to as well, so its their fault. You of course also need to supply the values that'll substitute the question marks, and then actually run the query, before you get any results.)

Anyway, to the point. PDO looks for the ? or :name markers, and replaces them (in order or by name, respectively) with the values you specify. When the values are inserted into the query string, they're first processed to escape anything that could be used for injection attacks.

It's similar to using mysql_real_escape_string() (or the weaker addslashes()) on a value before using it in a query, but PDO does it automatically and is better at it.

Saturday, November 13, 2021
 
Zach
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share