允许用户传递表名和列名，同时防止SQL注入

问题描述

我有一个(内部网)网页(ASP.NET C#)，该页面允许用户在SQL Server中的特定db上创建表，表名和列名是自由文本字段，并且确定了实际的结构通过一些下拉菜单.我知道将create table语句作为纯字符串与我的文本框中的文本连接在一起时容易受到SQL注入的影响.鉴于我正在传递尚未存在的db对象而不是参数的实际名称，除了在连接字符串之前检查每个文本框是否包含非法字符之外，还有什么方法可以防止注入的可能性?

解决方案

这是

I have an (intranet) web page (ASP.NET C#) which allows users to create a table on a specific db in SQL-Server, table name and column name/s are free text fields and the actual structure is determined by a few dropdowns. I am aware passing the create table statement as pure string concatenated with the text from my textboxes is susceptible to SQL injection. Given that I am passing the actual names of db objects which don't yet exist rather than parameters is there any way to prevent the possibility of injection other than checking each text box for illegal characters before concatenating the string?

解决方案

This is what QUOTENAME() was created to solve. You pass in your column and table names as parameters in to QUOTENAME() and then you use the output of it to represent objecs in your database in a dynamic sql query.

//The evil name tries to expliot code like:
//  set @sql = N'CREATE TABLE [' + @tablename + N'] (Foo int)'
var evilName = "someName] (Foo int); Drop table students --";

var query = @"
declare @sql as nvarchar(max)
set @sql = N'CREATE TABLE ' + QUOTENAME(@tablename) + N' (Foo int)'
exec sp_executesql @sql
";
using(var connection = new SqlConnection(ConnectionString))
using(var command = new SqlCommand(query, connection))
{
    command.Parameters.Add("@tablename", SqlDbType.NVarChar, 128).Value = evilName ;
    connection.Open();
    command.ExecuteNonQuery();
}

The query that will be executed on the server will be

CREATE TABLE [someName]] (Foo int); Drop table students --] (Foo int)

which creates a table with a valid table name and does not drop my other table.

这篇关于允许用户传递表名和列名，同时防止SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！