Input rows and SQL Injection

One Star

Input rows and SQL Injection

Hi Talend Forums,
What is the best way to prevent SQL injection when passing a variable through to an input component?
Looking at restful services, I've noticed that passing a variable to tSetGlobalVar and then using that variable as part of the SQL for the input (as suggested in the Rest services tutorial), leaves the database open for SQL Injection

AKA, for this resource path:
GetCustomer/{EmailAddress}
I just tried this in a URL:
GetCustomer/name@isp.com'%20%20OR%20EmailAddress%20=%20'othername@isp.com
Which would pass through to the database, and end up generating the XML for both entries instead of the one record as expected
Am I best to try and use preparedstatements somehow, or is there an easy way to escape the string safely before it's executed in the SQL?
Cheers
Seventeen Stars

Re: Input rows and SQL Injection

The best way is using the tPostgresqlRow component because you can use prepared statement parameter. Unfortunately this is not implemented in the normal input components.
The content of prepared statement values will never interpreted as SQL code!
To get the result set you have to configure this component in the advanced settings and put the ResultSet into a schema column of type Object. In the next step use tParseRecordset to retrieve the columns from your query result set.
One Star

Re: Input rows and SQL Injection

What am I meant to do for other database connections (inputs mainly)................