Home » Server Options » Text & interMedia » How to prevent SQL Injection in OracleText SQL statement (Oracle10g)
How to prevent SQL Injection in OracleText SQL statement [message #398543] Thu, 16 April 2009 10:49 Go to next message
leon_buijsman
Messages: 13
Registered: March 2009
Location: Rotterdam
Junior Member

Hi,

On a site I am developing site visitors will have the possible to search a table. Part of the statement is:

where contains (text,''' || p_text || ''') > 0

p_text is the variable passed by the oracle procedure.

When you pass apple''')>0 or some statement or contains(text,'''blabla in the p_text statement you can still end up with a correct SQL Statement, but doing something completely different as intented.

Is there some way in the contains string that I can do to prevent sql injection, but still provide people the possibility to do boolean search using OR/AND/{}/etc.

Kind regards,

Leon

Re: How to prevent SQL Injection in OracleText SQL statement [message #398544 is a reply to message #398543] Thu, 16 April 2009 10:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
To some extent, dbms_assert will help.
http://www.oracle-base.com/articles/10g/dbms_assert_10gR2.php
Re: How to prevent SQL Injection in OracleText SQL statement [message #398566 is a reply to message #398543] Thu, 16 April 2009 14:40 Go to previous message
Barbara Boehmer
Messages: 7974
Registered: November 2002
Location: California, USA
Senior Member
Security issues with sql injection happen when an unvalidated user-supplied string is executed dynamically. If you can eliminate the dynamic execution, then there is no problem. There is no need for dynamic sql in the snippet of code that you posted. You can probably just do whatever you are doing staticly. For example if you are opening a ref cursor, then you can do so safely with something like:

open your_refcursor for
select * from your_table
where contains (text, p_text) > 0;

instead of opening it dynamically, allowing sql injection like this:

open your_refcursor for
'select * from your_table
where contains (text,''' || p_text || ''') > 0';



Previous Topic: Space in Lexer
Next Topic: CTX_DOC
Goto Forum:
  


Current Time: Mon Sep 01 21:38:09 CDT 2014

Total time taken to generate the page: 0.12047 seconds