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
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;
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
create table "t].[" (... )where clause or if it really exists it will be quoted. Checked in this db<>fiddle. But thank you a lot, you made me think of really bad examples and possibilities.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
