SQL Injection question

[walkerline117]

Hi,

what is the mechanism in Snaplogic to prevent SQL injection, e.g. for Oracle.
Do snaps, e.g. Oracle select, prevent SQL injection?

Thanks

[tstack · Accepted solution]

Here’s some guidance for writing SQL for Snaps:

Simple Substitutions

If you need to use an input document value for a value in SQL, you can directly reference it with a JSON-Path. You do not need to make the property an expression. For example, to use the property “name” in the input document, you would write the following:

SELECT * FROM mytable WHERE name = $name

Don’t write something like the following, which would allow for a SQL injection:

"SELECT * FROM mytable WHERE name = \'" + $name + "'"

If you need to reference a pipeline parameter or do a simple transform with an expression, you can use the .eval() JSON-Path syntax, like so:

SELECT * FROM mytable WHERE id = $.eval(parseInt(_idToLookup))

Or, you can insert a Mapper snap upstream from the SQL snap and write an expression to put into the input document.

Dynamically constructing a SQL query

Unfortunately, you cannot use a path at all points in a query, so you will need to make the SQL property an expression in some cases. For example, if you need to pull the table name or column name from somewhere, then you’ll need to use an expression. However, you should still be using paths for substituting values in the generated SQL. For example, if we wanted to parameterize the table name in the above query, you could do:

"SELECT * FROM " + $table + " WHERE name = $name"

The Snap will evaluate the expression and still do the path substitutions.

Details

The Snaps that support SQL will preprocess the query to extract any JSON-Paths and convert them to bound parameters. For example, in this query:

SELECT * FROM mytable WHERE name = $name

The query is converted to something like the following before it is turned into a prepared statement for the DB:

SELECT * FROM mytable WHERE name = ?

The Snap will then evaluate the JSON-Path to get the value to bind to the given parameter in the prepared statement.

[walkerline117]

Thanks so much, this is very helpful