How to exit a stored procedure in PDW

Question

I am working in PDW AU5. I am writing a stored procedure that needs dynamic SQL (because of variable database names). So, to prevent SQL injection, I am doing a simple test of the input parameter value to the stored procedure. If the test fails, I want to exit the stored procedure. Normally on SMP, I would do this with RETURN -1. However, RETURN isn't allowed in stored procs on PDW. How can I get around this?

Answer 1

You'll need to wrap the code around an IF statement and in the section where it's supposed to be RETURNed just make sure the IF condition isn't met and the whole code block is bypassed and onto the end of the stored procedure.

e.g.

CREATE PROCEDURE dbo.uspDynamicSQL
( @UserField varchar(255) )
AS

IF CHARINDEX(@UserField) > 0 THEN
BEGIN

-- YOUR SQL CODE AS NORMAL

END;

It's not pretty but it works.

Answer 2

You can try to use the QUOTENAME function to escape the single quote character on the input string to stop the Dynamic SQL from injected.

DECLARE @userfield VARCHAR(255) = 'abc'' ;SELECT 1; '
DECLARE @sql NVARCHAR(4000)

SET @sql = 'SELECT ' + QUOTENAME(@UserField, '''')

PRINT @sql

EXEC (@sql)

See another post about how to prevent SQL Injection how to prevent SQL Injection