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 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