The SQLExecutor / SQLCreator should support bind variables rather than string substitution in SQL statements for the following two reasons:
I suggest making bind or string substitution an option within the SQLExecutor.
+18Already 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? :)
+12This 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.
+2Hi 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.
+12You'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.
+13@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.
+12@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_ENDThe 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
+13@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.
+13An 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.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
