SQL INJECTION - Connector
Hi
I am having trouble avoiding sql injection through connectors. I happen to send as a parameter, a field in a form that is used by my clients as an input for comments and observations. They started inserting apostrophes characters ( ' ) which are the ones used in postgresql (my database) in functions.
So let's say i Call my function like Select approveRequest('$req_no', '$name', '$observations');
The variable observations is mapped to a text area input where my user inserted code like "PRODUCT DESCRIPTION: 2LT 2x2' KETCHUP"
My connector fails as after the second number 2, there is an apostrophe.
Any help will really be appreciated. Thanks in advance.
UPDATE
I have been reading about this topic and "prepared statements" seem to be a solution for the apostrophe or special character insertion problem. I am supposed to do something like this:
import groovy.sql.Sql def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr","oracle.jdbc.pool.OracleDataSource") (I am working with postgres, this is a example)
Should I insert this code while defining my connector in the "query or batch script" textbox. But then, the variable sql, wasn't it declared in a previous step while defining the connector? Isn't there a way to already have that variable. I am a bit confused.
Thanks.
Hello, you can escape your single quotes, since it is a special character in sql syntax. Replacing ' by '' (two single quotes) or \' (backslash in front of the single quote) should do it for Postgres. You can try do it on your text before including it in the insert, or using the postgres replace function. For the other part of your question - you should avoid using Groovy scripts to do SQL queries. If you want to use prepared statements, you should develop a new connector. Hope this helps, Haris
Comments
I can't use two single quotes for postgres, that's not going to work.
I was thinking on replacing those special characters, just before I invoke my connector function. But still I prefer the prepared statement solution.
Could your please refer me to a good guide on how to develop a connector on bonita 6+ and how to implement it using prepared statements?
Thanks for your answer above and thanks in advance for any further help.
Comments
Hello, what DB are you using and which connector?
I am using Bonita 6.2.1, PostgreSql 9.2.0.
When I am defining my connector there is only two options available for postgreSQL, I am using the one with 9.2
Thanks in advance for any help and for your answer.