Question 114857 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to create a Role with execute() keeping it safe from sql injection?

I want my python app admin to be able to create a role in postgreSQL in it’s interface.
I made this method :

 def creer_user(self,login,pwd,groupe):
        queryrole = "CREATE ROLE %s WITH PASSWORD %s LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1;"
        self.cursor.execute(queryrole,(login,pwd))

        querygrant = f"GRANT '{G.APP}', %s TO %s;"
        self.cursor.execute(querygrant,(groupe,login))

But it’s not working because the parameters in execute() wrap my strings like ‘rolename’.
And I read it need to be "rolename" or rolename

I can do this :

queryrole = f"CREATE ROLE "{login}" WITH PASSWORD %s LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1;"
        self.cursor.execute(queryrole,(pwd,))

But I believe it’s not SQL injection safe.

Is there a simple solution ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Finally Found a way to do it using cursor.mogrify(), and formating the ' as " in the resulting string. Not really elegant. But I believe it's at least SQL injection proof.

    query = self.cursor.mogrify("CREATE ROLE %s WITH PASSWORD %s LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1;",(login,pwd))
## mogrify generate bynary code, so we need to convert it.
query_format = query.decode('utf-8').replace(f"'{login}'",f""{login}"")
self.cursor.execute(query_format)

  2. 0

    Use format() in SQL:

    format(
   'CREATE ROLE %I WITH PASSWORD %L LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1',
   'rolenane',
   'password'
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search