[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

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?
0
ram_0218
Asked:
ram_0218
  • 8
  • 4
  • 3
  • +1
1 Solution
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
slightwv (䄆 Netminder) 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now