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 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog