How to Prevent SQL Injection When Updating an SQLite Database via JSON POST in Python?

SQL Injection Protection in Python

Question:

To protect against SQL injection vulnerability, how can a string received from the user and sent via JSON POST be sanitized for safe update operations in an SQLite database within Python?

Answer:

Utilizing Parameterized Queries

The recommended approach to mitigate SQL injection risk is to use parameterized queries. Python's sqlite3 module supports this via placeholder parameters (?). Instead of concatenating user input directly into the SQL statement, it is passed as a parameter, separating user input from SQL syntax.

Revised Code:

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

By using a parameterized query, any potentially malicious characters or metacharacters in the label input are automatically escaped, ensuring they are interpreted as literal text rather than SQL commands.

The above is the detailed content of How to Prevent SQL Injection When Updating an SQLite Database via JSON POST in Python?. For more information, please follow other related articles on the PHP Chinese website!