SQL injection for Stored Procedure

Question

I'm evaluating the possibility of doing SQL injection for my sp.

I have tried using this to do SQL injection but didn't manage to inject (meaning the injection text was inserted to table as per normal):

data'; DROP TABLE my_table; --

How should I try SQL injection? Or the SP is so safe that SQL Injection is prevented somehow?

My reduced SP as below:

@ID int,
@AIType varchar(1),
@parent varchar(20),
@child varchar(20),
AS
BEGIN

SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY
    UPDATE AI_Grouping
    SET AIType=@AIType, 
        parent=@parent,
        child=@child,
    WHERE ID=@ID
END TRY
BEGIN CATCH
    -- Catch exceptions
END CATCH
END

EDIT:

In case it helps - at front end, I have a field length validation which is consistent with SP variable type. Some fields are limited max 8 chars, some are max 20 chars (like above example). Maybe the injection example that I tried above is a bad example, because the length is more than 20 chars... The ultimate question is, is my SP vulnerable to SQL injection or not?

The only way you can do a SQL Injection into an SP is if you use dynamic query and concatenate the varchar parameter on it. A stored procedure without dynamic query is pre compiled, therefore you can't change the command with a parameter. — Jorge Campos, Apr 26 '18 at 03:59
Thanks for commenting @JorgeCampos. Are you trying to say my SP is safe from SQL injection? — xzk, Apr 26 '18 at 04:01
Here, a good reading: http://www.oracle.com/technetwork/database/features/plsql/overview/how-to-write-injection-proof-plsql-1-129572.pdf — Jorge Campos, Apr 26 '18 at 04:03

Jorge Campos · Accepted Answer · 2018-04-26T04:29:11.787

From the article: How to write SQL injection proof PL/SQL

Distinguishing between compile-time-fixed SQL statement text and run-time-created SQL statement text

We define the term compile-time-fixed SQL statement text to mean the text of a SQL statement that cannot change at run time and that can be confidently determined by reading the source code. More precisely, it is the text of a SQL statement that is a PL/SQL static varchar2 expression14. The value of a PL/SQL static varchar2 expression cannot change at run time and could be precomputed at compile time.

The SQL statement text for embedded SQL is composed by the PL/SQL compiler and cannot change at run time. Therefore, embedded SQL definitely executes only compile-time-fixed SQL statement text15.

However, it can easily be arranged that any of PL/SQL’s methods for executing dynamic SQL will, at a particular call site, execute only compile-time-fixed SQL.

So your code as it is, is safe.

To distinguish between compiled-time-fixed SQL and run-time-created SQL here are two samples:

compiled-time-fixed SQL

CREATE PROCEDURE remove_emp (p_employee_id NUMBER) AS
  BEGIN
     -- here the delete command is immutable, therefore sql injection safe
     DELETE FROM employees
        WHERE employees.employee_id = p_employee_id;
  END;

run-time-created SQL

CREATE PROCEDURE remove_emp (p_employee_id VARCHAR2) AS
  BEGIN
     -- here the delete command is dynamically created allowing 
     -- sql injection
     execute immediate 'DELETE FROM employees
        WHERE employees.employee_id = ' || p_employee_id || ';';
  END;

SQL injection for Stored Procedure

1 Answers1