I have a script in FME Server that gets called by a URL with parameters for the WHERE clause. The querystring on the URL looks like this:
some_example.fmw?Year=2010&Neighbourhood;=Kensington-Cedar%20Cottage
The WHERE clause in the database reader then looks like this:
neighbourhood = '$(Neighbourhood)' AND year(event_date) = $(Year)
Are these placeholders "parameterized" in the sense that an SQL injection would fail? If not, is there a way to do this?
Thanks
The best thing is to use a Python Parameter. Depend to your case, you can reject the request or not with an exception. In your Python script, you can check the value of parameter and you link the Where clause with your Reader.
I agree with @julien, but sanitzing SQL "by hand" is both difficult and risky, as it is too easy to miss fringe cases. I think the most imporant thing here would be to only connect to the database with a user having extremely limited rights, ideally only SELECT rights on that specific table.
Either way, I'd think long and hard before exposing a local instance of FME Server to the world. Rolling out your own front-end application using FME Server internally would make a lot of sense in this scenario, as you would severly limit the possibilites for abuse.
Obligatory xkcd :-)
David
Those are good answers, gentlemen. I think the better answer is for FME to provide a way to "parameterize" the parameters. If FME Server is going to be used for the world, it is naive to be able to pass it a querystring like:
Year=2010&Neighbourhood;=Kensington-Cedar%20Cottage
...and have no validation on the FME side.
Those are good answers, gentlemen. I think the better answer is for FME to provide a way to "parameterize" the parameters. If FME Server is going to be used for the world, it is naive to be able to pass it a querystring like:
Year=2010&Neighbourhood;=Kensington-Cedar%20Cottage
...and have no validation on the FME side.
My take would be that the validation isn't the responsability of FME, but that of the database. This is due to the number of SQL dialects and all the possible use cases. If the database doesn't accept your where-clause, it will raise an exception that propagates to FME and terminates the translation. It is then up to the user or the calling application how to handle the exception (abort, retry, etc).
Having said that, I fully agree that FME should support bind variables in all SQL statements, which is something I've flagged with Safe before. This would not only improve security against SQL injections, but also boost performance for SQLExecutors etc.