Avoid SQL injection when creating user with SQLAlchemy Core

105 Views Asked by At

When creating a user with psycopg, I would create the following composite in order to avoid SQL injection:

from psycopg.sql import SQL, Identifier, Literal

username = "test_user"
password = "test_password"
query = SQL(
    "CREATE USER {username} WITH ENCRYPTED PASSWORD {password};"
).format(username=Identifier(username), password=Literal(password))

This can be tested in the scope of a psycopg.Connection:

print(query.as_string(connection))  # CREATE USER "test_user" WITH ENCRYPTED PASSWORD 'test_password';

What is the recommended way to do the same with sqlalchemy? I tried quoted_name and bind_params, but the former was not successful:

from sqlalchemy import quoted_name, text

query = text(
    f"CREATE USER {quoted_name(username, True)} WITH ENCRYPTED PASSWORD :password;"
).bindparams(password=password).compile(compile_kwargs={"literal_binds": True})

print(query)  # CREATE USER test_user WITH ENCRYPTED PASSWORD 'test_password';

(Changing the second parameter of quoted_name to False or None yields the same result.)

0

There are 0 best solutions below