SQLExecutor support for bind variables

Great great idea. Agree completely.

Already was filed at Safe, but I've linked PR 64274 to this Idea so we can update here when we progress on it.

Any progress on this in new versions? :)

Anything regarding bind variables in 2018?

This feature came up again in my case queue today. So I thought I'd share a quick update that we don't have anything currently planned to implement support for bind variables in the SQLExecutor.

Hi Steve, thanks for the information, even though it's not what I wanted to hear, it's good to know.

Hi all

Coming across SQL Injection more and more both inside FME and with other applications which is no great surprise. How many votes do we need to get this on the radar ?

Now trying to setup FME server with a SQL executor in the workbench I am quite worried. Just now I tested entering ' OR 1=1 in the attributemanager right before the SQL executor and all those records where rejected. Meaning the value ' OR 1=1 had succesfully manipulated the query, or in other words, the injection was succesfull. This was one of the few posts I came across regarding this issue. I see this as a major vulnerability.

We are working on something related to bind variables. Stay tuned!

Hi Steve, any word on when this will be available?

Thank you,

David

You'll be happy once you get your hands on 2023.0. We've made some enhancements in this area. Beta's will be out in mid April or so. Please do reach out if you have some questions when you get your hands on it! I'll be honest... I've not played with the new enhancements but hope to before they release in FME 2023.0 ~ May time frame.

@steveatsafe​: I'm not seeing anything related to bind variables or SQL parameter handling in the FME 2023 release notes, and even the new FME 2023.1 SQL tutorial uses string interpolation. What were you referring to that was supposed to come out with FME 2023.0?

In the meantime, parameters need to be escaped and FME provides no built-in tool to do that, so any query that relies on user-specified parameters for things like filtering on the database side (not all tables are small enough to fit in FME's RAM) is going to be difficult to get right and and injection-prone if done poorly.

@vlroyrenn​  Check out the documentation here and in particular the section - Custom SQL Binding: https://docs.safe.com/fme/html/FME-Form-Documentation/FME-ReadersWriters/oracle_spatial/feature-types-w.htm?Highlight=RESULT_SET

We have a new section of parameters/variables that can be added to the SQLCreator/SQLExecuter (for Oracle only):

FME_BINDS_BEGIN 
   HELLO VARCHAR(300)
   TEST VARCHAR(300)
   LINES RESULT_SET 
   POLYS RESULT_SET
FME_BINDS_END

The RESULT_SET is a new definition to pass back to FME the results from the SQL executed in the BEGIN/END section. Example:

BEGIN
   :HELLO := 'HELLOWORLD';
   :TEST := 'This is a test';
   OPEN :POLYS FOR SELECT * FROM FMEENGINE_71210_POLYS;
   OPEN :LINES FOR SELECT * FROM FMEENGINE_71210_LINES;
END;

To get the bind in the SQL ensure you define it in the new FME_BINDS* section and have a related attribute of the same named user attribute coming into the Transformer (if applicable) to pass on that value in the bind.  An Article is coming... My apologies for the missing examples that will soon appear in an article.  

Please give it a good test and let us know how it goes! 

Steve

Hi Steve. Why is this for Oracle only? Will it also be available for MS-SQL?

@tombirch74​ : Seems to be because it's actually using procedural SQL to bind what appears to be static values with the query (at least, going by what the doc says), instead of binding parameters or attribute values of the initiator into the query. From what I'm reading, it's not the kind of bind variables the initial idea was talking about.

@steveatsafe​ : I might be reading this wrong, but I'm not entirely sure how this solves the issue from the original idea. The problem is that, if my initiator contains a user-specified string to lookup, like "Safe Software", and I'm trying to use that to use that in the WHERE clause of my SQL query, the only way I can do that right now is with string interpolation:

SELECT brand_name, product_name FROM products
WHERE brand_name = '@Value(brand_name)'

The expectation is that a value like "Safe Software" is going to create a query that looks like this:

SELECT brand_name, product_name FROM products
WHERE brand_name = 'Safe Software'

But if the query is an untrusted user parameter, then it's trivial for a malicious user to set the brand name to something like "Unsafe Software'; DROP TABLE products; --", and break the database...

SELECT brand_name, product_name FROM products
WHERE brand_name = 'Unsafe Software'; DROP TABLE products; --'

...which is why constructing SQL statements with string interpolation is almost universally regarded as a bad idea, unless you have full knowledge of the escaping rules of your database engine. FME doesn't provide any "SQLParameterEscaper" transformer for this sort of task, so it's "up to users" to do it by hand, but that mostly means most people won't bother or will do it poorly.

All SQL RDBMS I know of support prepared statements, where the SQL query is sent with placeholders and the parameters are sent out of band, so that there is no possible way of altering the query's syntax, and that seems to be what the initial idea was talking about. "FME_BINDS" doesn't solve the issue because you still need to inject the string values in the textual query; they're not sent as bound variables and/or collections.

An alternate (though definitely less practical, more of a stopgap solution) would be to have a built-in transformer to perform parameter escaping using the best practices and built-in tools for each DB engine.

• libpq (PostgreSQL) has PQescapeLiteral() (for numbers and non-quoted literals) and PQescapeStringConn() (for quoted literals like strings, but also dates, number ranges, arrays, etc.)
• libmysqlclient (MySQL/MariaDB) has mysql_real_escape_string_quote() (for strings)
• Oracle (whether through OCI or JDBC/ODBC) don't have native string escaping methods, they both officially only support prepared statements
• SQLServer only has OdbcCommandBuilder.QuoteIdentifier for table identifiers, not for literals, so this would also need to be done via careful string manipulation.
• etc.

That might be easier to implement (no drastic changes to SQLExecutor/SQLCreator needed) than adding proper support to the existing transformers. I don't think it would be the best solution, but between that and nothing at all, I would rather go down this route.