CODEWITHSUNDEEP
sqlsql-servert-sql
datadawg2000
datadawg2000

Reputation: 51

Avoiding SQL injection with sp_executesql

I have the below stored procedure that we've identified as being vulnerable to SQL injection, but I'm not sure how to achieve the same kind of thing without injection. Any ideas appreciated

CREATE PROCEDURE [dbo].[usp_Trun_Tab]
    (@TrunTableSchema VARCHAR(100),
     @TrunTableName VARCHAR(254))
WITH EXECUTE AS OWNER
AS
BEGIN  
    DECLARE @SQL NVARCHAR(400)

    SET @SQL = 'TRUNCATE TABLE '+ @TrunTableSchema +'.'+@TrunTableName 

    EXEC sp_EXECUTESQL @SQL
END

Upvotes: 0

Views: 295

Answers (3)

Dale K
Dale K

Reputation: 27202

You are looking for the QUOTENAME function which ensures the entered names are escaped:

And for neatness you could define your input parameters as sysname since thats what they are.

CREATE PROCEDURE [dbo].[usp_Trun_Tab]
(
    @TrunTableSchema sysname
    , @TrunTableName sysname
)
WITH EXECUTE AS OWNER
AS
BEGIN  
    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @SQL nvarchar(max);

    SET @SQL = 'TRUNCATE TABLE ' + QUOTENAME(@TrunTableSchema) + '.' + QUOTENAME(@TrunTableName);

    EXEC sp_EXECUTESQL @SQL;

    RETURN 0;
END;

Upvotes: 4

Sparky
Sparky

Reputation: 15085

I agree with previous comments, not sure benefit of the procedure. However, you could also check that the table and schema both exist...

declare @x int

select @x = count(*)
from INFORMATION_SCHEMA.TABLES
where table_schema =  @TrunTableSchema
and table_name = @TrunTableName

if @x = 1
begin 
  -- Do your truncation logic here
end

This way, you will only truncate if parameters are valid.

Quotename works as well, this is just an alternative solution, as I tend to validate parameters prior to executing commands

Upvotes: 0

astentx
astentx

Reputation: 6751

The way I prefer is to do the easiest validation: check against the metadata. You will get only existing objects.

Like this:

create procedure sp_trunc(
  @TrunTableSchema VARCHAR(100),
  @TrunTableName VARCHAR(254)
)
as
begin

  declare @sql nvarchar(1000);

  select
    @sql = 'truncate table '
    + quotename(table_schema) + '.'
    + quotename(table_name) + ''
  from information_schema.tables
  where table_type = 'BASE TABLE'
    and table_schema = @TrunTableSchema
    and table_name = @TrunTableName
  ;

  if @sql is null
    return; --handle not exist
  else
    EXEC sp_EXECUTESQL @sql;

end;

db<>fiddle here

Upvotes: 1

Related Questions