[jira] [Created] (ZEPPELIN-2894) Error using PreparedStatement in GetUserList with certain JDBC drivers

["Janus D (JIRA)" <ji...@apache.org>]

Janus D created ZEPPELIN-2894:
---------------------------------

             Summary: Error using PreparedStatement in GetUserList with certain JDBC drivers
                 Key: ZEPPELIN-2894
                 URL: https://issues.apache.org/jira/browse/ZEPPELIN-2894
             Project: Zeppelin
          Issue Type: Bug
          Components: zeppelin-server
            Reporter: Janus D
             Fix For: 0.7.3, 0.8.0


Using Shiro authentication with the build-in JDBC Realm (org.apache.shiro.realm.jdbc) and a PostgreSQL data source (org.postgresql.ds.PGSimpleDataSource - postgresql-42.1.4.jar) Zeppelin fails to load any suggestions from the user list in the notebook permission form with the following error:
{code:java}
ERROR [2017-09-01 11:05:44,432] ({qtp1206883981-48} GetUserList.java[getUserList]:255) - Error retrieving User list from JDBC Realm
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
{code}
[ZEPPELIN-2769|https://issues.apache.org/jira/browse/ZEPPELIN-2769] introduced a mechanism to prevent SQL injection, but unfortunately table names can not be parameterised in PreparedStatements. Also the column variable "username" might be interpreted as a quoted string and the final list would contain x times "username" instead of the real names (see Figure).
Other solutions preventing SQL injections mostly rely on other libraries (e.g. escaping) or assumptions (e.g. widely database access). 
I would consider to revert the changes. The SQL statement for getting the user list should not be a security threat as the query parameters will be parsed server-sided from the authenticationQuery, no user input will be provided at all.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)