การป้องกัน SQL Injection

Imagine you're talking to a database using a special language called SQL. Sometimes, bad actors can trick your application into sending unexpected SQL commands to the database.

This trick is called SQL Injection (SQLi). It happens when user input is treated as part of the SQL command itself, rather than just data.

SQLi can lead to:

• Data theft or modification
• Bypassing login screens
• Gaining control over the database server

Let's say a login form takes your username and password. The application might build a SQL query like this to check if you exist:

SELECT * FROM users WHERE username = 'your_username' AND password = 'your_password';

What if 'your_username' isn't just a name, but also a piece of SQL code?

A common mistake is building SQL queries by directly combining (concatenating) user input with the query string. Here's a simplified example in Java:

Now, imagine a malicious user enters this as their username:

admin' OR '1'='1

And any text for the password. When combined with the vulnerable query, it becomes:

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'any_password';

The OR '1'='1' part makes the condition always true, effectively bypassing the password check and logging the attacker in!

The best defense against SQL Injection is using Prepared Statements (also known as Parameterized Queries).

They separate the SQL logic from the user-provided data. Instead of directly inserting input, you use placeholders (like ?) in your query.

The database then understands that anything provided for these placeholders is pure data, not part of the command.

Here's how you'd use a prepared statement to safely query for a user by their ID. Notice the ? placeholder.

When you use a prepared statement:

• Separation of concerns: The SQL query structure is sent to the database first.
• Data vs. Code: The database 'pre-compiles' the query. Then, when you provide values for the placeholders, it treats them strictly as data.
• Automatic Escaping: The database automatically handles any special characters in your input, preventing them from being interpreted as SQL commands.

This ensures that malicious input like ' OR '1'='1 cannot alter the query's intent.

Let's fix our vulnerable login example using prepared statements. Now, even if a user tries to inject SQL, it will be treated as part of their username/password, not as a command.

Prepared statements aren't just for SELECT queries. You should use them for ALL SQL operations that involve user input:

• INSERT statements (e.g., adding a new user)
• UPDATE statements (e.g., changing a user's profile)
• DELETE statements (e.g., removing data)

Always assume user input is malicious until proven otherwise. Parameterized queries are your first line of defense.

Which of the following code snippets correctly uses parameterized queries to prevent SQL Injection when searching for a product by name?

Great job! In this lesson, you learned about:

• What SQL Injection is and its dangers.
• How vulnerable applications can be exploited by concatenating user input directly into SQL queries.
• The importance of using Prepared Statements (Parameterized Queries) as the primary defense mechanism.
• How prepared statements separate data from code, preventing malicious input from altering query logic.

Always use prepared statements for any SQL operation involving user input to keep your backend secure!