Question

Proper escaping in database writers ?

lifalin2016
Rank
Userlevel 1
Badge +22

Hi list.

I'm writing data to an SQL Server database, with full schema targets.

My schema names often include a dash (e.g. "RSF-Aflob"), which freaks FME out, as it doesn't perform any form of escaping. It errs both when writing data, and even when I import attribute names to a transformer.

Now, proper escaping is an integral part of database manipulations, so I wonder why it's omitted in FME ? FME do know which database type is in question (here: SQL Server which uses [xxx]), so escaping the proper way really is possible.

Can anyone shed some light on this issue ? Should it not be promoted as a improvement request ?

Cheers.

-- Cheers, Lars I.

8 replies

david_r
Rank
Userlevel 4

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.

lifalin2016
Rank
Userlevel 1
Badge +22

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.

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

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

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.

 

-- Cheers, Lars I.
david_r
Rank
Userlevel 4

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

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 :-(

david_r
Rank
Userlevel 4

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.

 

That's interesting, thanks for sharing that observation.

M
Badge +2

@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

lifalin2016
Rank
Userlevel 1
Badge +22

@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.

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

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

-- Cheers, Lars I.

Reply


Community Stats

28,661
Posts
109,323
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.