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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now