Bind native query error. EclipseLink(JPA 2.1)

Question

Error binding variables to native query. EclipseLink(JPA 2.1)

 String sql = "ALTER ROLE ?1 WITH ENCRYPTED PASSWORD 'xxx'"; //(not working)
 //String sql = "ALTER ROLE ? WITH ENCRYPTED PASSWORD 'xxx'"; (not working)

 Query query = em.createNativeQuery(sql);

 String text = txtUsername.getText();


 query.setParameter(1, text);

 em.getTransaction().begin();


 int executeUpdate = query.executeUpdate();
 em.getTransaction().commit();

Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 12 Error Code: 0 Exception in thread "AWT-EventQueue-0" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Call: ALTER ROLE ? WITH ENCRYPTED PASSWORD 'xxx' bind => [1 parameter bound] Query: DataModifyQuery(sql="ALTER ROLE ? WITH ENCRYPTED PASSWORD 'xxx'") Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 12 Error Code: 0 Call: ALTER ROLE ? WITH ENCRYPTED PASSWORD 'xxx'

Not working: String sql = "ALTER ROLE ? WITH ENCRYPTED PASSWORD 'xxx'" — Ramon González, Aug 23 '17 at 12:56
https://stackoverflow.com/questions/37973915/hibernate-positional-parameters-zero-based — Nathaniel Johnson, Aug 23 '17 at 13:00
I would not be surprised if this is the JDBC driver basically saying you cannot put parameters in that part of the SQL. Many will not allow parameters in the SELECT clause, or FROM clause for example — , Aug 23 '17 at 13:00
The gist of that question revolves around whether the index for parameters is zero or one based. Quick and dirty, change positional to 0. See if it works. — Nathaniel Johnson, Aug 23 '17 at 13:04
Not working with 0: Exception in thread "AWT-EventQueue-0" java.lang.IllegalArgumentException: You have attempted to set a parameter at position 0 which does not exist in this query string ALTER ROLE ? WITH ENCRYPTED PASSWORD 'xxx'. — Ramon González, Aug 23 '17 at 13:10
In general you cannot use parameters where postgresql expects an identifier (role name, table name, column name, etc), only where it expects a value. — Eelke, Aug 23 '17 at 13:13
Not working trying to bind password: Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 31 Error Code: 0 Call: ALTER ROLE role WITH PASSWORD ? bind => [1 parameter bound] Query: DataModifyQuery(sql="ALTER ROLE role WITH PASSWORD ?") at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:308) — Ramon González, Aug 23 '17 at 13:19
this has nothing to do with JPA as it is your database/driver throwing the exception. What does the docs say for this statement? — Chris, Aug 23 '17 at 13:46
https://stackoverflow.com/questions/4518210/alter-user-password-via-jdbc-problems-with-passes-containing-question-marks?rq=1 — Ramon González, Aug 23 '17 at 14:05

score 0 · Answer 1 · answered Aug 07 '18 at 14:59

The username and password can not be parameterized in this query. The alternate will be to write a stored function and call the function. A store function I found online that can be modified to suite your needs is :

CREATE OR REPLACE FUNCTION save_user(
in_username text,
in_password TEXT) returns bool
SET datestyle = 'ISO, YMD' -- needed due to legacy code regarding datestyles
AS $$
DECLARE

    stmt text;
    t_is_role bool;
BEGIN
    -- WARNING TO PROGRAMMERS:  This function runs as the definer and runs
    -- utility statements via EXECUTE.
    -- PLEASE BE VERY CAREFUL ABOUT SQL-INJECTION INSIDE THIS FUNCTION.

   PERFORM rolname FROM pg_roles WHERE rolname = in_username;
   t_is_role := found;

   IF t_is_role is true and t_is_user is false and in_pls_import is false THEN
      RAISE EXCEPTION 'Duplicate user';
    END IF;

    if t_is_role and in_password is not null then
            execute 'ALTER USER ' || quote_ident( in_username ) ||
                 ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
                 || $e$ valid until $e$ ||
                  quote_literal(now() + '1 day'::interval);
    elsif  t_is_role is false THEN
        -- create an actual user
            execute 'CREATE USER ' || quote_ident( in_username ) ||
                 ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password)
                 || $e$ valid until $e$ || quote_literal(now() + '1 day'::interval);
   END IF;

   return true;

END;
$$ language 'plpgsql' SECURITY DEFINER;

Bind native query error. EclipseLink(JPA 2.1)

1 Answers1