Secure Coding & OWASP Top 10 for Backend · レッスン

SQLインジェクションの防止

SQLインジェクションの仕組みを理解し、パラメーター化クエリやプリペアドステートメントを使って堅牢な防御を実装します。

「SQLインジェクションの防止」はCoddyKit上の無料Secure Coding & OWASP Top 10 for Backendレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSecure Coding & OWASP Top 10 for Backend学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Secure Coding & OWASP Top 10 for Backendコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

What is 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

How SQLi Works: A Login Example

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?

The Vulnerable 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:

public class Main {
  public static void main(String[] args) {
    String username = "admin"; // User input
    String password = "pass123"; // User input
    
    // DANGEROUS: String concatenation
    String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "';";
    
    System.out.println("Executing query:\n" + query);
    // In a real app, this query would go to the database.
  }
}

Crafting the Malicious Input

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!

Introducing Prepared Statements

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.

Prepared Statements in Action

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

public class Main {
  public static void main(String[] args) {
    int userId = 123; // User input
    
    // Secure: Using a placeholder for the value
    String sql = "SELECT name, email FROM users WHERE id = ?;";
    
    // In a real application, you'd 'prepare' this SQL
    // and then 'set' the parameter value (userId) separately.
    System.out.println("Prepared SQL: " + sql);
    System.out.println("Parameter 1 (id): " + userId);
    // The database treats 'userId' as a value, not SQL code.
  }
}

How Parameters Prevent SQLi

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.

Securing the Login Example

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.

public class Main {
  public static void main(String[] args) {
    String username = "admin' OR '1'='1"; // Malicious input
    String password = "any_password"; // Malicious input
    
    // SECURE: Using prepared statement with placeholders
    String sql = "SELECT * FROM users WHERE username = ? AND password = ?;";
    
    // Simulating how parameters are set (conceptual)
    System.out.println("Prepared SQL: " + sql);
    System.out.println("Parameter 1 (username): " + username);
    System.out.println("Parameter 2 (password): " + password);
    
    System.out.println("\nDatabase will look for a user with the literal username 'admin' OR '1'='1' and the given password. This user likely won't exist. Attack thwarted!");
  }
}

Beyond Login: All SQL Operations

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.

Quick Check: Secure Query

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

Recap: SQL Injection Prevention

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!

よくある質問

「SQLインジェクションの防止」レッスンは無料ですか？

はい。「SQLインジェクションの防止」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Secure Coding & OWASP Top 10 for Backendコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Secure Coding & OWASP Top 10 for Backendコースには全4レッスンが含まれています。

「SQLインジェクションの防止」で何を学びますか？

SQLインジェクションの仕組みを理解し、パラメーター化クエリやプリペアドステートメントを使って堅牢な防御を実装します。ブラウザで直接実行するハンズオンコードでSecure Coding & OWASP Top 10 for Backendを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Secure Coding & OWASP Top 10 for Backendを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSecure Coding & OWASP Top 10 for Backendは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン1/4です。

「SQLインジェクションの防止」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSecure Coding & OWASP Top 10 for Backendレッスンでコードを書いて実行できますか？

はい。すべてのSecure Coding & OWASP Top 10 for Backendレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← Secure Coding & OWASP Top 10 for Backendに戻る