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

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 ?

CodePudding user response：

Use format() in SQL:

format(
   'CREATE ROLE %I WITH PASSWORD %L LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1',
   'rolenane',
   'password'
)

CodePudding user response：

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)