I'm updating a flow to allow users to inject events into our Tharsterns Database. All is working fine except before I roll it out I want to sanitise any user input (At the moment entering, for instance "Peter O'Toole" will cause errors, and I dread to think what would happen if their name was Peter O Drop Table Users; etc.)
Obviously, I don't expect our users to be so fiendish but for peace of mind and ISO reasons I'm after any standardish way of sanitising the input.
Sanitising User Input
Sanitising User Input
Peter O'Toole causes errors because the name is enclosed in single quotes in the SQL command, and the single quote in the name is interpreted as the end of the value. You should therefore escape the single quotes and in SQL that is done by doubling them. So even Peter O''Drop Table Users would not have any dreaded side effects.
In the "Database connect" element you can build your SQL statement with a script expression and in there you can double the single quotes. Assuming the name is in a metadata field the code will look something like this:
var name = job.getVariableAsString("[Metadata.Text....something...]");
var re = /'/g;
name = name.replace(re,"''"));
"UPDATE TableName SET ColumnName='"+name+"' WHERE ...something...";
Note that in a script expression you do not return anything. You just specify the variable name or the string that contains the required value.
Freddy
In the "Database connect" element you can build your SQL statement with a script expression and in there you can double the single quotes. Assuming the name is in a metadata field the code will look something like this:
var name = job.getVariableAsString("[Metadata.Text....something...]");
var re = /'/g;
name = name.replace(re,"''"));
"UPDATE TableName SET ColumnName='"+name+"' WHERE ...something...";
Note that in a script expression you do not return anything. You just specify the variable name or the string that contains the required value.
Freddy
Sanitising User Input
Hi Freddy,
yes, that's exactly the issue. I'm currently working around it with substituting a '' in. But I'm acutely aware getPropertyValue receiving a variant opens me up to similar problems with integers.
(Oh for parametrized query support)
yes, that's exactly the issue. I'm currently working around it with substituting a '' in. But I'm acutely aware getPropertyValue receiving a variant opens me up to similar problems with integers.
(Oh for parametrized query support)
Sanitising User Input
I should add, This particular functionality will be build as a generic script element as it has utility across a number of flows.
Sanitising User Input
Then do not use the "Database connect" element, but also perform the SQL query inside the script. Then you have full control over all the parameters and for which ones you have to escape the single quotes, and for which ones you do not.
Look in the "Database module" section of the "Scripting reference" and there you will find everything about creating Datasource objects and Statement objects.
Freddy
Look in the "Database module" section of the "Scripting reference" and there you will find everything about creating Datasource objects and Statement objects.
Freddy
Sanitising User Input
Hi Freddy,
It is a script element, I'm not using Database Connect. So it can be used across a number of flows.
For ISO 27001 and general security reasons I need to make sure it's safe when I start opening it up to some external users. ( And as it's features creep
)
The properties for this particular script element can be the variables or script element results brought in with:
s.getPropertyValue('Name').
As a result I have to sanitize strings and numbers as both of them are open to abuse (What I think are numbers could be strings otherwise)
At this time no numbers are in use with it, but they will be present in some other related scripts and I need a general policy for sanitizing inputs.
It is a script element, I'm not using Database Connect. So it can be used across a number of flows.
For ISO 27001 and general security reasons I need to make sure it's safe when I start opening it up to some external users. ( And as it's features creep
)The properties for this particular script element can be the variables or script element results brought in with:
s.getPropertyValue('Name').
As a result I have to sanitize strings and numbers as both of them are open to abuse (What I think are numbers could be strings otherwise)
At this time no numbers are in use with it, but they will be present in some other related scripts and I need a general policy for sanitizing inputs.