temp table..

ok i created a temp table like this:

  CREATE GLOBAL TEMPORARY TABLE CHAR_LIST (
  column1  VARCHAR2(50)) ON COMMIT DELETE ROWS;

now i've a proc like this:
 proc(comma_delimited_string IN varchar2) {
  insert into char_list select * from table(str2tbl('CANCELLED'));

 open cursor select * from maintable where id in (select column1 from char_list);
return cursor;

}

but the data inserted first time not getting deleted after the proc call is over? is that because i should issue a commit or rollbacK? the proc is only trying to do a select.. the reason for doing the above is to avoid sql injection issues.. (using bind variables)

i can issue commit but will it impatct in performance as for the select statement i'm issueing commit etc?
LVL 17
ram_0218Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The original question of:  i can issue commit but will it impatct in performance as for the select statement i'm issueing commit etc?

I believe has been answered in http:#a37045148


>>ok then i don't think i can use this option.. i want a kinda temp table where after select and return ref cursor i want the records in the temp table to be removed..


How will the procedure be called?  If a new Oracle 'session' will call the procedure only once a Global Temp table might still work.  Just change it to 'On commit preserve rows'.

Then the rows will be around as long as the session.
0
 
ram_0218Author Commented:
any other alternatives for handling the injection issue also appreciated..
0
 
slightwv (䄆 Netminder) Commented:
>>is that because i should issue a commit or rollbacK?

You need to 'commit'.  See the declaration:  ON COMMIT DELETE ROWS;

>>the reason for doing the above is to avoid sql injection issues.. (using bind variables)

How will this help wit sql injection?

>>but will it impatct in performance as for the select statement i'm issueing commit etc?

The only thing a commit might mess up is if you are doing other DML in the procedure. you might accidentally commit other changes.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ram_0218Author Commented:
thanks..

>>How will this help wit sql injection?

here's the pseudo code:
function(input_list varchar2) {
  open cursor for
 'select * from maintable where id in ('||input_list||')';
}

if you pass the input value as:
');drop table student;

will it not drop the table student as well when running that statement?
0
 
slightwv (䄆 Netminder) Commented:
No.  Oracle isn't as susceptible to injection as other databases.

Create a quick test and try it.
0
 
johnsoneSenior Oracle DBACommented:
If you do the commit, it will delete the rows of your table.  However, you have not selected the records yet.  The process of opening a cursor, simply returns a pointer, it does not actually fetch any results.

Whatever consumes the records from the cursor should commit when it is done consuming the records which would remove the records from the temporary table.
0
 
ram_0218Author Commented:
oh johnsone, very good point.. ok then i don't think i can use this option.. i want a kinda temp table where after select and return ref cursor i want the records in the temp table to be removed..

I'm actually trying an alternate solution for the other problem i faced here:
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_27416694.html

sligtwv, no.. i wrote a sample program and it's having issue:

here's the pseudo code:
function(input_list varchar2) {
  open cursor for
 'select id from maintable where id in ('||input_list||')';
}


pass parameter as:

'a' ) union (select id from othertable

then the query will become like this:
elect id from maintable where id in ('a') union (select id from other table)

and this query return will return more records, this is security vulnerability..
0
 
sdstuberCommented:
so,  this is a duplicate question?
0
 
ram_0218Author Commented:
sdstuber,

as you can see this is not a duplicate question.. the other question revolves around why the str2tbl function gettting executed multiple times which i unfortunately haven't been able to solve.. this question is related to the temp tables which i'm investigating as a potential variation to the str2tbl fix..
0
 
sdstuberCommented:
in your other question you discussed using a temp table derived from the parsed values,  hence my concern that you were creating two questions for the same idea
0
 
ram_0218Author Commented:
yes you are kind of correct. but i don't think they are same questions though.

Here's my idea:

using str2tble output all the results into a temp table then join that temp table when running the select for the cursor..
0
 
slightwv (䄆 Netminder) Commented:
>>pass parameter as: 'a' ) union (select id from othertable

OK, that will cause problems but the 'drop table' form of injection won't work.
0
 
ram_0218Author Commented:
Yes.. I'm trying to address that problem only..
0
 
sdstuberCommented:
if str2tbl doesn't return the correct results then using it populate a temp table won't help
0
 
ram_0218Author Commented:
ok looks like there's a misunderstanding here.. i never said str2tbl doesn't return correct results.. it does..

the only issue in my previous question was related to number of times the function being called..
0
 
ram_0218Author Commented:
guys any help?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.