OraFAQ Forum: Text & interMedia » How to prevent SQL Injection in OracleText SQL statement

Home » Server Options » Text & interMedia » How to prevent SQL Injection in OracleText SQL statement (Oracle10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

How to prevent SQL Injection in OracleText SQL statement [message #398543]

Thu, 16 April 2009 10:49

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

Report message to a moderator

Re: How to prevent SQL Injection in OracleText SQL statement [message #398544 is a reply to message #398543]

Thu, 16 April 2009 10:51

Mahesh Rajendran
Messages: 10572
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

Report message to a moderator

Re: How to prevent SQL Injection in OracleText SQL statement [message #398566 is a reply to message #398543]

Thu, 16 April 2009 14:40

Barbara Boehmer
Messages: 7667
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';

Report message to a moderator

Previous Topic:	Space in Lexer
Next Topic:	CTX_DOC

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 00:39:41 CDT 2013

Total time taken to generate the page: 0.17060 seconds