Question

Google Spreadsheet Text to Postgres not treated as text

  • 15 March 2014
  • 2 replies
  • 0 views
G
Badge +1
Hi All,

 

 

I'm not Database Admin and I am getting some help with the SQL queries, but I'm updating a postgres database from values in a google spreadsheet. The values are stored as text in the spreadsheet and postgres but I'm having to use the "quote_literal" in the SQL below otherwise I get a text error when trying to write to the postgress database.  

 

 

The issue is that it does still fail when I use the kind of text in the google doc illustrated in this nice cartoon.

 

 

Is there a transformer I need to use get FME/Postgres to treat it correctly or is this just a bug in the way FME is talking to Postgres?

 

 

I'm using the Google Spreadsheet Reader and the SQL Executer to do the work.

 

 

 

 

 

UPDATE function_program_data_function_structure

 

SET text_data = quote_literal('@Value(fs_10_10_10_10)')

 

WHERE level_id=@Value(id) AND flag_no='10' AND group_no='10' AND group_element_no='10' AND field_no='10'

2 replies

takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

I'm not sure whether the SQL statement is correct, since not familiar with Postgres. But I guess FME has logged related error message when the SQL failed. Is there any hint in the error message?

 

 

Takashi
david_r
Rank
Userlevel 4
Hi,

 

 

this is, as you've noted, related to SQL injection attacks. The main problem is that the FME SQLExecutor doesn't bind variables, and as such all values are sent as string literals, leading to all kinds of potential problems. You therefore have to sanitze your attribute values before the SQLExecutor.

 

 

PostgreSQL and Oracle (and perhaps also other) supports an escaping mechanism that you might try. For PostgreSQL, I'd try dollar quoted string contants (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING), e.g.:

 

 

UPDATE function_program_data_function_structure

 

SET text_data = $my_string_tag$@Value(fs_10_10_10_10)$my_string_tag$

 

WHERE level_id=@Value(id) AND flag_no='10' AND group_no='10' AND group_element_no='10' AND field_no='10'

 

 

(For reference, this is the equivalent in Oracle (http://www.adp-gmbh.ch/ora/sql/q.html))

 

 

Please also consider writing Safe support to add your voice to the existing enhancement request for bind variables in the SQLExecutor :-)

 

 

David

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
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.