Which are the roles in a query string of apostrophe and quotation mark in SQL? How can the apostrophe be replaced?
Main interest is their specific roles in PostgreSQL.
Leaving apostrophe out messes SQL query, e.g,:
SELECT role FROM "USER_ROLES" WHERE "USER_ROLES".login = admin;
Gets a 'column' admin does not exist error, when admin is a value. Writing 'admin' corrects the problem:
SELECT role FROM "USER_ROLES" WHERE "USER_ROLES".login = 'admin';
Actual problem is using the query in another context:
<module-option name="rolesQuery" value="SELECT 'role', 'Roles' FROM "USER_ROLES" WHERE login='?'"/>
As requested, table definition is:
CREATE TABLE "USER_ROLES" (
login text NOT NULL,
role text,
CONSTRAINT "USER_ROLES_pkey" PRIMARY KEY (login ),
CONSTRAINT "USER_ROLES_login_fkey" FOREIGN KEY (login)
REFERENCES "USER" (login) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
PostgreSQL version 9.1.
You can replace single-quotes (') with dollar-quoting in PostgreSQL to avoid their special meaning in a different context. (Not sure, though, whether the dollar-sign has a special meaning, too):
<module-option name="rolesQuery" value="SELECT role FROM "USER_ROLES" WHERE login = $$?$$"/>
The other idea would be to use ' in place of single-quotes:
<module-option name="rolesQuery" value="SELECT role FROM "USER_ROLES" WHERE login = '?'"/>