Avoiding SQL Injection attacks using procedures

Userlevel 5
  • Community Manager
  • 21 replies

SQL injections are a common security issue when users are given the freedom to construct their queries. One way to avoid them is by creating a custom procedure that acts as a wrapper, eliminating any risk of injection.
Here is an example procedure for filtering hand_on.cust view using country filter.

CREATE PROCEDURE hands_on.parametrizedQuery(IN filter_string STRING NOT NULL) RETURNS (City STRING NOT NULL, Company STRING NOT NULL) AS     BEGIN        DECLARE STRING sql_string = 'SELECT City, CompanyName FROM hands_on.cust WHERE =';        EXECUTE IMMEDIATE sql_string AS City STRING, CompanyName STRING INTO #temptbl USING country=filter_string;        SELECT * FROM #temptbl;    END;;

You can call it using a CALL functionality

CALL hands_on.parametrizedQuery('China');;

Or, if the CALL is not available, you can query it like a table and specify the input variable name and value in a WHERE statement.

SELECT * from hands_on.parametrizedQuery where filter_string='China';;

0 replies

Be the first to reply!