How Can Parameterized Queries Prevent SQL Injection in Python's SQLite Interactions?

Protecting Against SQL Injection in Python

Problem:

You have a Python script that updates a char(80) value in an SQLite database. The string value is obtained directly from a user and passed to the script as a JSON payload. You are aware that this approach is vulnerable to SQL injection attacks.

Solution:

To protect against SQL injection, you can use parameterized queries in your update operation. Parameterized queries allow you to pass values to a SQL statement without including them directly in the query string. This prevents malicious input from disrupting the SQL syntax.

The Python sqlite3 library supports parameterized queries through the ? placeholder. You can pass values to the execute method as a tuple of values that correspond to the placeholders in the query.

Revised Code:

Here is a revised version of your setLabel method that uses a parameterized query:

def setLabel( self, userId, refId, label ):
    self._db.cursor().execute(
        """
        UPDATE items SET label = ? WHERE userId = ? AND refId = ?""",
        (label, userId, refId)
    )
    self._db.commit()

By using a parameterized query, you ensure that the user input is treated as a value rather than a part of the SQL statement, effectively preventing SQL injection attacks.

The above is the detailed content of How Can Parameterized Queries Prevent SQL Injection in Python's SQLite Interactions?. For more information, please follow other related articles on the PHP Chinese website!