Stored Procedure - running a INSERT with dynamic variables

1

I am trying to run something similar to this within a stored procedure in Oracle.

pWhereClasue2 is generic which I set depending on the table I am working on. The following code runs just if I have the proper defined where clause, but If I try to use this variable, I keep getting errors. I have tried to use ||, single quotes, but nothing has worked. I am not an expert at Stored Procedure. But any help would be appreciated. I am just trying to figure how I can keep this generic and have this code work with pWhereCLAUSE working

INSERT INTO ABC

SELECT * FROM ABCD pWhereCLAUSE to_char(dateOne, 'DD-MON-YYYY') and ROWNUM <= variable1;

commit;

2

This is a Microsoft SQL Server site so you might do better at Stackoverflow.

I suspect the only way a generic could work with any relational database is Dynamic SQL as a query plan needs the actual objects.

3

It would be something like this in SQL Server. Should be similar in Oracle

CREATE PROCEDURE
[dbo].[sp_HumanEventsBlockViewer]
(
@whereclause nvarchar(256) = N'City = ''Cartago''',

)

AS
BEGIN

DECLARE @sqlCommand nvarchar(1000)

SET @sqlCommand = 'SELECT * FROM Person.Address WHERE ' + @whereclause
EXECUTE sp_executesql @sqlCommand

END

4

SQL Injection

5

Only if

A) @whereclause is exposed to Users

AND

b) @whereclause isn’t checked for injection