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.)
sqlalchemy.schema.DDLwith%splaceholders for context -- which we want evaluated only at compile time, not when the query string is built, so the engine's dialect details are available.sqlalchemy.textand execute it. Database admin actions aren't really the use-case for SQLAlchemy (or most other ORMs).text(SQL(...))leads to aTypeErrorsincetextexpects a string or bytes-like object. In addition, if I have to usepsycopgconstructs, I can just do everything inpsycopginstead of mixing the two libraries. Nonetheless, I have hope that SQLAlchemy Core supports what I am asking for (SQLAlchemy ORM not, of course).as_string()method to stringify it prior before passing it totext().