Proper escaping in database writers ?

Are you using the SQLExecutor, maybe? If so, you are responsible for escaping the statements yourself, depending on the syntax of your database back end, e.g.

select * from "RSF-Aflob"."MyTable"

The regular readers and writers should be able to handle this automatically. If not, I would expect it to be a case for Safe support.

select * from "RSF-Aflob"."MyTable"

That's not really proper escaping, as escaping always should be native, since ANSI support can be turned off.

It seems that using or not using "bulk mode" makes a difference.

If turned off, proper escaping is used. Bulk mode seems to be the culprit.

I completely agree that it's not a very good solution, which is why I proposed the idea to support bind variables more than 4 years ago: https://knowledge.safe.com/idea/23829/sqlexecutor-support-for-bind-variables.html

Not only would it be much more secure, but it would also come with performance gains.

Still not implemented :-(

That's interesting, thanks for sharing that observation.

@lifalin2016 FME is somewhat inconsistent in the quoting of database table and attribute names - depending on the database. But the FME SQL Server writer does (or should) quote the SQL. I've attached a simple example and below are the table create for bulk mode Off an On:

CREATE TABLE [Special%^&-Two] ([SQL] varchar(10), [SECOND] integer, [another-name] integer)

The inserts are slightly different for Bulk Mode = Yes but still quoted:

exec sp_executesql N'INSERT INTO [Special%^&-Two] ([SQL], [SECOND], [another-name]) VALUES (@P1, @P2, @P3)',N'@P1 varchar(10),@P2 int,@P3 int','123',1234,9876

I copied these from the event SQL Server Management Studio event log.

As @david_r mentions, for SQLExecutor/Creator and any other custom SQL, it's you're responsibility to quote the values - depending on the database. For SQL Server, FME will quote table names and attributes, if you select them from the lists:

SELECT [another-name],[SECOND],[SQL] FROM dbo.[Special%^&-Two];

Can you include a small example workspace where this is not working for you? We can then try and address that.

Example (FME 2020): specialcharacters.fmw

Hi Mark.

The error occurred in the writer itself, not the SQL-transformers, where setting an unescaped schema name with a dash included in "Table Qualifier" threw an error.

As I mentioned elsewhere, it seems like non-bulk mode does escape it, but bulk mode doesn't.

I'll have a look at submitting some simple proof of concept.

I did a little screen dump story of what I'm seeing in the 2020.0 import schema wizard.

SELECTING A TABLE IN AN UNESCAPED SCHEMA IN THE IMPORT WIZARD.pdf