Wrong quoting in PostgreSQL query

[Leonardo Rossi]

I have a problem with sql quoting on this query:

database_user = "test\"quo'te"
dtabase_pass = "pwd\"quo'te"
engine.execute("""CREATE USER %s WITH PASSWORD %s """, database_user, database_pass)

I tried to execute on pgadmin3 and this query returns errro:

CREATE USER 'testquote3' WITH PASSWORD 'pwd"quo''te2'

but, this query works:

CREATE USER "testquote3" WITH PASSWORD 'pwd"quo''te2'

Traceback:

Traceback (most recent call last):
  File "/home/vagrant/.virtualenvs/invenio2/bin/inveniomanage", line 9, in <module>
    load_entry_point('invenio==2.1.0.dev20150305', 'console_scripts', 'inveniomanage')()
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/base/manage.py", line 103, in main
    manager.run()
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/script/__init__.py", line 148, in __call__
    res = super(SignalingCommand, self).__call__(*args, **kwargs)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/base/scripts/database.py", line 93, in init
    database_pass=current_app.config['CFG_DATABASE_PASS'],
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/ext/sqlalchemy/utils.py", line 392, in initialize_database_user
    database_user, database_pass)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1751, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text
    statement, parameters
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "'test"quo''te'"
LINE 1: CREATE USER 'test"quo''te' WITH PASSWORD 'pwd"quo''te' 
                    ^
 'CREATE USER %s WITH PASSWORD %s ' (u'test"quo\'te', u'pwd"quo\'te')

[Leonardo Rossi]

I have the same problem if I execute:

engine.execute("""grant all privileges on database %s to %s """, database_name, database_user)

It's expected that db name and user are double quoted instead of single quoted:

grant all privileges on database  'mydb' to 'user'

the right query is:

grant all privileges on database  "mydb" to "user"

[Mike Bayer]

this is an invalid use of bound parameters as these are identifiers, not values, and also has nothing to do with SQLAlchemy. The handling of "%s" or other DBAPI paramstyles is a function of the DBAPI in use, most likely psycopg2 in this case.

[Leonardo Rossi]

which is the right way to execute this query without have problems with quotes?

[Mike Bayer]

engine.execute("""CREATE USER "%s" WITH PASSWORD "%s" """ %  (database_user, database_pass))

[Mike Bayer]

well you'd need to escape the quotes within those values.

[Mike Bayer]

here's all of psycopg2's docs on this: http://initd.org/psycopg/docs/usage.html#query-parameters I would gather that they'd advise you register some new "identifier" type of some kind for these particular kinds of values, as they aren't "SQL literals".