Oracle sql - stored procedure - concat a variable in a string

Question

I have a stored procedure of this kind

create or replace
PROCEDURE AAA 
(
    p_BBB       IN  VARCHAR,
    ...
)
AS 
T_QUERY varchar2(3000);
BEGIN
     OPEN A_CUR FOR SELECT ... BBB like '%' || p_BBB || '%';

   T_QUERY := 'SELECT BBB like %'|| p_BBB ||'% '; 

END AAA;

The problem is that while the first query is correctly processed, the concat between the strings in T_QUERY gives me error (invalid character).

Neither using concat() works, the T_QUERY will be pass to another stored procedure that performs the query.

Can anyone help me??

Solution 1

you may try

T_QUERY :='SELECT BBB like ''%'|| p_BBB ||'%''';

Solution 2

Above solution is correct. You use it like this.

create or replace 
PROCEDURE AAA  
( 
    p_BBB       IN  VARCHAR, 
    ... 
) 
AS  
T_QUERY varchar2(3000); 

BEGIN 

     T_QUERY :='SELECT BBB .. like ''%'|| p_BBB ||'%'''; 
     OPEN A_CUR FOR T_QUERY ; 
     ----
     ----

END AAA; 

Comments

[Codo]

That's a recipe for creating an SQL injection vulnerability.

[Jacob]

@Codo So what is the best way to avoid SQL injection in this case?

[Codo]

The best way would be to pass the query and the parameter separately and to use bound parameters when executing it (EXECUTE IMMEDIATE p_query USING p_param). In addition, the execution plan will be cached that way. If a single query string is unavoidable, replace all single quotes with two single quotes, put a single quote at the start and at the end and run it through DBMS_Assert.Enquote_Literal. And read and adhere to How to Write Injection Proof PL/SQL.