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 theoracle.pgql.lang.ir.PgqlUtils.printIdentifier(String identifier)
method.
Parent topic: Executing PGQL Queries Against the Graph Server (PGX)
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.
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
- For
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 throughPgxPreparedStatement.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.
Parent topic: Security Tools for Executing PGQL Queries
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);
Parent topic: Security Tools for Executing PGQL Queries