使用动态sql插入数据时出错 [英] Error in inserting data using dynamic sql

问题描述

我正在使用动态sql在表中插入数据。以下是我的程序。

Hi, I am using dynamic sql to insert data in the table. Following is my procedure.

CREATE PROCEDURE [dbo].[AddClient]
    @schemaName nvarchar(20),
	@Name nvarchar(20),
	@des nvarchar(30)
	
AS

declare @sql as nvarchar(max)
set @sql = N'insert into ['+@schemaName+N'].[Products]
(Id,Name,ProductDesc,Price,CategoryId)
values(1,"' +@Name + N'","' + @des + N'",20000,1)'

print @sql

exec (@sql)
RETURN 0

当我执行程序时，它会显示1行影响的按摩。并且还给出了无效columnName'Mobile（参数@name的值）'

的错误在按摩窗口中，打印以下内容

when I execute the procedure, it shows the massage that 1 row affected. And also gives the error that invalid columnName 'Mobile(value of parameter @name)'
In massage window, following is printed

insert into [jay].[Products]
(Id,Name,ProductDesc,Price,CategoryId)
values(1,"asad","sdsdsd",20000,1)

    (1 row(s) affected)
    
Msg 207, Level 16, State 1, Line 3
Invalid column name 'asad'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'sdsdsd'.

我认为我的语法错误。如果我只插入int或数字数据，则不会发生此问题。问题在于字符串。请帮帮我。

I think my syntax is wrong. The problem does not occur if I insert only int or number data. The problem is with string. Please help me.

推荐答案

改变这个：

Change this:

set @sql = N'insert into ['+@schemaName+N'].[Products] (Id,Name,ProductDesc,Price,CategoryId)
values(1,"' +@Name + N'","' + @des + N'",20000,1)'

对此：

To this:

set @sql = N'insert into ['+@schemaName+N'].[Products] (Id,Name,ProductDesc,Price,CategoryId)
values(1,''' +@Name + N''',''' + @des + N''',20000,1)'

你不能使用双引号，你需要使用单引号。



您还可以参数化动态SQL，这样您就不必处理引用。有关详细信息，请查看本教程。

在SQL Server中执行动态SQL命令 [ ^ ]

您的代码容易受到 SQL注入 [ ^ ]。



从不使用字符串连接构建一个SQL查询。 始终使用参数化查询。



从存储过程中，使用sp_executesql [ ^ ]用参数执行查询。



对于无法作为参数传递的项目，例如模式名称，那么你需要根据已知良好的列表验证它们。在这种情况下，请确保存在具有指定名称的架构，并且它包含预期的 Products 表。



这样的东西应该有效：

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

From within a stored procedure, use sp_executesql[^] to execute the query with parameters.

For items which can't be passed as parameters, such as the schema name, then you need to validate them against a known-good list. In this case, ensure that there is a schema with the specified name, and that it contains the expected Products table.

Something like this should work:

CREATE PROCEDURE dbo.AddClient
(
    @schemaName nvarchar(20),
    @Name nvarchar(20),
    @des nvarchar(30)
)    
AS
BEGIN
DECLARE @Sql as nvarchar(max), @ValidatedSchema sysname, @ValidatedTable sysname;
    
    SET NOCOUNT ON;
    
    SELECT
        @ValidatedSchema = QuoteName(S.name),
        @ValidatedTable = QuoteName(T.name)
    FROM
        sys.schemas As S
        INNER JOIN sys.tables As T
        ON T.schema_id = S.schema_id
    WHERE
        S.name = @schemaName
    And
        T.name = 'Products'
    ;
    
    If @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Invalid schema name: "%s"', 16, 1, @schemaName);
        Return;
    END;
    
    SET @sql = N'INSERT INTO ' + @ValidatedSchema + N'.' + @ValidatedTable
        + N' (Id, Name, ProductDesc, Price, CategoryId)'
        + N' VALUES (1, @Name, @des, 20000, 1)'
    ;
    
    PRINT @sql;
    
    EXEC sp_executesql @sql,
        N'@Name nvarchar(20), @des nvarchar(30)',
        @Name = @Name, @des = @des
    ;
END

这篇关于使用动态sql插入数据时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！