Pb with SQL and special characters (like ')

General
1

Hi,
I have a problem with sql injections in my postgresql database;
Here is the request which works most of the time :
INSERT INTO tdb (tweet, author, date, url, tags) VALUES ('{{{msg.txttweet}}}','{{msg.tweet.user.screen_name}}','{{msg.tweet.created_at}}','https://twitter.com/{{msg.tweet.user.screen_name}}/status/{{msg.tweet.id_str}}','{{msg.hst}}')

I use the {{{}}} to avoid html transformation but sometimes when I have some characters in the tweet ( a string) I have this kind of error message :

  • The tweet :
    RT @ajaltamimi: New post on publication that appeared not too long ago documenting lives of various male & female 'martyrs' of I…

  • The error message :
    msg : error "error: syntax error at or near "martyrs""

It seems that the ' character is the problem, and futhermore I don't know why the & is replace by &amp despite the use of {{{}}}

If you have an idea ...
Thx

2

Have you tried using the Parameterized query syntax instead of the Template ?
From what i read in this article it sanitizes the input .. I havent tested it though

Examples in the node's help site

INSERT INTO tdb (tweet, author, date, url, tags) VALUES ($txttweet, $author, $date, $url, $tags)

Function node :

msg.queryParameters.txttweet = msg.txttweet;
msg.queryParameters.author = msg.tweet.user.screen_name;
msg.queryParameters.date = msg.tweet.created_at;
msg.queryParameters.url = `https://twitter.com/${msg.tweet.user.screen_name}/status/${msg.tweet.id_str}`
msg.queryParameters.tags= msg.hst;
3

Or you should be able to escape the 'martyrs' like this,
'RT @ajaltamimi: New post on publication that appeared not too long ago documenting lives of various male & female ''martyrs'' of I...'

So prior to the template add a change node set to
change msg. txttweet
search for '
repolace with ''

4

Thank you both.

Finally I choose the simple solution : msg.txttweet = txttweet.replace(/'/g, "''").replace(/&/g, "and");

5

keep in mind that there could be other symbols that could break your sql query
-- for example that is used for comments

try it with a test string in msg.txttweet to see if it breaks
single quote followed by two dashes

'--
6

You're right. Thx so much.

7

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.