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
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 — user3973283

deepak mahapatra deepak mahapatra · Accepted Answer · 2018-08-07T14:59:38

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 Answers