Using Prepared Statements

1. Using Prepared Statements

Great work so far! In the previous video, we learned how to run basic queries using Statements. But here's a critical question: what happens if a user enters malicious data into our application? This is exactly how SQL injection attacks happen, and they're one of the most common security vulnerabilities in web applications.

2. The problem with Statements

Let's start by looking at the problem. With a regular Statement, we often build queries by concatenating strings. Here we're building a query where the title comes from a variable. This might work for simple cases, but it's dangerous. If the title variable contains unexpected input, an attacker could inject malicious SQL into our query. For example, imagine a user enters this as the title. The single quote closes our string early, the OR one equals one condition is always true, and the dashes comment out the rest of our query. The result? Instead of finding one book, this query returns every book in our database!

3. SQL Injection

But it gets worse. What if the attacker wants to destroy our data? They could enter something like this. Now our innocent-looking SELECT statement becomes two commands: an empty SELECT, followed by a DELETE that wipes our entire books table. You might think: can't we just limit the database user's permissions? That helps, but it's not enough. Even with read-only access, attackers can still query sensitive tables, such as users, and steal credentials or private data.

4. Prepared Statements

That's where Prepared Statements come in. A PreparedStatement is a precompiled SQL statement that uses placeholders instead of concatenated strings. Here's how it works. First, we define our query using a question mark as a placeholder. Then, we create the PreparedStatement by passing our SQL string to the prepareStatement method. Next, we use setString to safely bind our parameter. Notice the index starts at one, not zero. Finally, we call executeQuery just like before. The key difference is that the database treats our parameter as pure data, never as executable SQL. Special characters like quotes and semicolons are escaped automatically. So, even if someone enters malicious input, such as our earlier DELETE attack, it is treated as a literal string to search for.

5. Working with multiple parameters

When we have multiple parameters, we simply add more placeholders and set each one by position. Here we're searching by title and publication year. We use setString for the title at position one, and setInt for the year at position two. Each data type has its own setter method, which also helps prevent type-related errors.

6. Let's practice!

Time to put these concepts into practice and secure our queries!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.