CVE-2023-47128: SQL Injection in piccolo

Background

Piccolo is an object-relational mapping and query builder library for Python that supports asyncio. It provides a convenient way to interact with databases and perform CRUD operations. However, prior to version 1.1.1, Piccolo is vulnerable to SQL Injection via f-strings when handling named transaction savepoints. Although it is unlikely for end developers to expose the savepoints name parameter to users, it is not impossible. Exploiting this vulnerability would grant a malicious user direct access to the database, allowing them to modify data based on the permissions associated with the database user. This could lead to various actions such as reading sensitive data, inserting arbitrary data, and potentially gaining unauthorized access to the underlying server.

Vulnerability Detail

The vulnerability in Piccolo arises from the insecure handling of named transaction savepoints in all database implementations. By abusing this functionality, an attacker can inject malicious SQL queries via f-strings. This can lead to unauthorized access to the database and the ability to manipulate data at the level of permissions associated with the database user. The potential consequences include reading sensitive data, modifying existing records, and even gaining a shell on the underlying server. The severity of this vulnerability is rated as critical with a CVSS score of 9.1.

pip install --upgrade "piccolo==82679eb8cd1449cf31d87c9914a072e70168b6eb"

Workaround

As a workaround, if upgrading to the latest version is not immediately possible, developers can sanitize and validate the savepoints name parameter to prevent SQL Injection. This can be done by using parameterized queries or input validation techniques to ensure that only valid and safe values are used. Here is an example of using parameterized queries with Piccolo:

from piccolo import Model
from piccolo.columns import Varchar

class MyModel(Model):
    name = Varchar()

    @classmethod
    async def create_savepoint(cls, name):
        query = f'SAVEPOINT {name}'
        await cls.raw(query).run()

By using parameterized queries, the input value is treated as a parameter rather than being directly interpolated into the SQL query, mitigating the risk of SQL Injection.

Conclusion

In conclusion, the Piccolo library prior to version 1.1.1 is vulnerable to SQL Injection via f-strings when handling named transaction savepoints. This vulnerability can lead to unauthorized access to the database and the ability to modify data at the level of permissions associated with the database user. It is crucial to upgrade to version 1.1.1 or later to mitigate this vulnerability. For further support on vulnerability remediation, you can reach out to DevNack.com.

See every vulnerability recorded for piccolo →

FAQ