Home » SQL & PL/SQL » SQL & PL/SQL » LIKE with variable
LIKE with variable [message #186403] Mon, 07 August 2006 16:40 Go to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Considering this procedure:

create or replace PROCEDURE      MOS_USER_SEARCH
(SEARCHKEY VARCHAR2) IS
  CURSOR c_username (SEARCHKEY VARCHAR2)
  IS 
  SELECT * FROM MOS_USERS WHERE MOS_NAME LIKE '%Bart%';
  r_user c_username%ROWTYPE;


how would I define the searchkey in the SELECT:

SELECT * FROM MOS_USERS WHERE MOS_NAME LIKE '%SEARCHKEY%';


is making the SEARCHKEY being handled as string instead of variable.

How is it properly handled in Oracle?
Re: LIKE with variable [message #186404 is a reply to message #186403] Mon, 07 August 2006 16:44 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SELECT * FROM MOS_USERS WHERE MOS_NAME LIKE '%'||SEARCHKEY||'%';
Re: LIKE with variable [message #186477 is a reply to message #186404] Tue, 08 August 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you didn't want to hard parse every single execution of the cursor, AND leave yourself vulnerable to SQL injection attacks into the bargain, you could try:

CREATE OR REPLACE PROCEDURE MOS_USER_SEARCH
(P_SEARCHKEY IN VARCHAR2) IS
  v_searchkey  varchar2(30);

  CURSOR c_username (CP_KEY VARCHAR2) IS 
  SELECT * FROM MOS_USERS WHERE MOS_NAME LIKE cp_key;

  r_user c_username%ROWTYPE;
BEGIN
  v_searchkey := '%'||p_searchkey||'%';

  FOR REC IN c_username(v_searchkey) LOOP
...
..
.
Re: LIKE with variable [message #186482 is a reply to message #186477] Tue, 08 August 2006 02:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't think SQL-injection can take place when you use a cursor with cursor variables.
The risk of SQL-injection comes into play when using dynamic sql.
testcase:
create or replace procedure faq_test
(p_search in varchar2
) is
  cursor c
  ( b_search in varchar2
  ) is
    select 'Found' text
    from   dual
    where  'RIGHT' like '%'||b_search||'%'
  ;
  r  c%rowtype;
begin
  dbms_output.put_line('Concatenated text: '||'%'||p_search||'%');
  open c(p_search);
  fetch c into r;
  close c;
  dbms_output.put_line('Result: '||r.text);
end faq_test;

No matter what I feed it, I can't get an erroneous result.


Also, I don't think the cursor is hard-parsed over and over, because of the fact that a cursorvariable is used.

[Updated on: Tue, 08 August 2006 02:29]

Report message to a moderator

Re: LIKE with variable [message #186485 is a reply to message #186403] Tue, 08 August 2006 02:35 Go to previous messageGo to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Thanks for all the comments!

Just a real newbie question, sorry for that...

Isn't there something like sprintf, mysql_real_escape_string (in PHP) in Oracle?
Re: LIKE with variable [message #186493 is a reply to message #186482] Tue, 08 August 2006 03:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looks like you're right on both counts there Frank.

Embarassed

Apologies Scott - my criticism was unjustified.

I can see I need to bruch up on some of my Oracle fundamentals.
Previous Topic: How to deal with DATE format?????????
Next Topic: not worked concat_all function
Goto Forum:
  


Current Time: Fri Dec 09 13:47:02 CST 2016

Total time taken to generate the page: 0.46375 seconds