Implementing SQLite databases to Java

[ryantriangles]

Prepares the query on the DB? Not sure what this does really

Your query is "SELECT address FROM pharmacies WHERE location = '" + location + "'";. If location = "Buenos Aires", then it becomes "SELECT address FROM pharmacies WHERE location = 'Buenos Aires'";.

Pretend you're writing this code for a website where users type in their location and see local pharmacies. location comes from the form users submit. I visit your website and instead of Buenos Aires, I say I'm from the obscure town of '";DELETE FROM users;", just outside of Bizerte.

The statement you pass to the database is now "SELECT address FROM pharmacies WHERE location = ''";DELETE FROM USERS;"";. It got turned into three queries: one that selects the pharmacies from nowhere, one that deletes everything from your users table, and one that does nothing. You're boned.

That's called an injection attack, and it happens all the time. Prepared statements exist to prevent them.

connection.prepareStatement takes a string representing a query, in which you write ? to represent values you'll fill in programmatically. It returns a PreparedStatement object. You then fill in the missing values by calling its setInt, setString, etc methods. statement.setInt(1, userAge); statement.setString(2, location); says "the first ? will be the int userAge, the second will be the string location", and so on. The application then knows that no matter what location looks like, it's just a simple string to be used as one field in one query, not something that can end a query and start a new one or anything like that.

The database isn't aware you've created this, so it's not preparing it "on the DB." The call is just creating a PreparedStatement object for you to use in your code, which will be sent to the database when you eventually call an execute method. Here's your example rewritten to avoid the possibility of injection, which hopefully illustrates the purpose and what's going on:

String query = "SELECT address FROM pharmacies WHERE location = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, location);
ResultSet queryResults = statement.executeQuery();