18.11 Security Tools for Executing PGQL Queries

To safeguard against query injection, bind variables can be used in place of literals while printIdentifier(String identifier) can be used in place of identifiers like graph names, labels, and property names.

• Using Bind Variables
  There are two reasons for using bind variables:
• Using Identifiers in a Safe Manner
  When you create a query through string concatenation, not only literals in queries pose a security risk, but also identifiers like graph names, labels, and property names do. The only problem is that bind variables are not supported for such identifier. Therefore, if these identifiers are variable from the application's perspective, then it is recommended to protect against query injection by passing the identifier through the oracle.pgql.lang.ir.PgqlUtils.printIdentifier(String identifier) method.

18.11.1 Using Bind Variables

There are two reasons for using bind variables:

• It protects against query injection.
• It speeds up query execution because the same bind variables can be set multiple times without requiring recompilation of the query.

To create a prepared statement, use one of the following two methods:

• PgxGraph.preparePgql(String query) : PgxPreparedStatement
• PgxSession.preparePgql(String query) : PgxPreparedStatement

The PgxPreparedStatement (package oracle.pgx.api) returned from these methods have setter methods for binding the bind variables to values of the designated data type.

Consider the following example:

PreparedStatement stmnt = g.preparePgql(
  "SELECT v.id, v.dob " +
  "FROM MATCH (v) " +
  "WHERE v.firstName = ? AND v.lastName = ?");
stmnt.setString(1, "Camille");
stmnt.setString(2, "Mullins");
ResultSet rs = stmnt.executeQuery();

Each bind variable in the query needs to be set to a value using one of the following setters of PgxPreparedStatement:

• setBoolean(int parameterIndex, boolean x)
• setDouble(int parameterIndex, double x)
• setFloat(int parameterIndex, float x)
• setInt(int parameterIndex, int x)
• setLong(int parameterIndex, long x)
• setDate(int parameterIndex, LocalDate x)
• setTime(int parameterIndex, LocalTime x)
• setTimestamp(int parameterIndex, LocalDateTime x)
• setTimeWithTimezone(int parameterIndex, OffsetTime x)
• setTimestampWithTimezone(int parameterIndex, OffsetDateTime x)
• setArray(int parameterIndex, List<?> x)

Once all the bind variables are set, the statement can be executed through:

• PgxPreparedStatement.executeQuery()
  • For SELECT queries only
  • Returns a ResultSet
• PgxPreparedStatement.execute()
  • For any type of statement
  • Returns a Boolean to indicate the form of the result: true in case of a SELECT query, false otherwise
  • In case of SELECT, the ResultSet can afterwards be accessed through PgxPreparedStatement.getResultSet()

In PGQL, bind variables can be used in place of literals of any data type, including array literals. An example query with a bind variable to is set to an instance of a String array is:

List<String> countryNames = new ArrayList<String>();
countryNames.add("Scotland");
countryNames.add("Tanzania");
countryNames.add("Serbia");

PreparedStatement stmnt = g.preparePgql(
  "SELECT n.name, n.population " +
  "FROM MATCH (c:Country) " +
  "WHERE c.name IN ?");

ResultSet rs = stmnt.executeQuery();

Finally, if a prepared statement is no longer needed, it is closed through PgxPreparedStatement.close() to free up resources.

18.11.2 Using Identifiers in a Safe Manner

When you create a query through string concatenation, not only literals in queries pose a security risk, but also identifiers like graph names, labels, and property names do. The only problem is that bind variables are not supported for such identifier. Therefore, if these identifiers are variable from the application's perspective, then it is recommended to protect against query injection by passing the identifier through the oracle.pgql.lang.ir.PgqlUtils.printIdentifier(String identifier) method.

Given an identifier string, the method automatically adds double quotes to the start and end of the identifier and escapes the characters in the identifier appropriately.

Consider the following example:

String graphNamePrinted = printIdentifier("my graph name with \" special % characters ");
PreparedStatement stmnt = g.preparePgql(
  "SELECT COUNT(*) AS numVertices FROM MATCH (v) ON " + graphNamePrinted);