在 ASP.Net 中防止 SQL 注入 [英] Preventing SQL Injection in ASP.Net

问题描述

我有这个代码

UPDATE OPENQUERY (db,'SELECT * FROM table WHERE ref = ''"+ Ref +"'' AND bookno = ''"+ Session("number") +"'' ') 

我将如何防止 SQL 注入?

How would I prevent SQL Injections on this?

更新

这就是我正在尝试的

SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con); 
cmd.Parameters.AddWithValue("@ref", 34);

出于某种原因，我尝试添加的所有内容似乎都不起作用，我不断收到下面提到的 SQL 命令.

For some reason everything I try and add it doesn't seem to work I keep getting SQL Command mentioned below.

错误是这样的

'SqlCommand' is a type and cannot be used as an expression

我正在接管其他人的工作，所以这对我来说是全新的，我想以正确的方式做事，所以如果有人可以提供更多帮助，让我的上述查询免受 SQL 注入的影响，那么请这样做.

I'm taking over someone else's work so this is all new to me and I would like do things the right way so if anyone can provide any more help on how to make my query above safe from SQL injections then please do.

更新 2

我按照 VasilP 的说法添加了代码

I added in the code as VasilP said like this

Dim dbQuery As [String] = "SELECT * FROM table WHERE ref = '" & Tools.SQLSafeString(Ref) & "' AND bookno = '" & Tools.SQLSafeString(Session("number")) & "'"

但是我收到一个错误工具没有声明我需要指定一个特定的命名空间才能工作吗?

But I get an error Tools is not declared do I need to specify a certain namespace for it to work?

更新

有没有人知道如何最好地使我的查询免受 SQL 注入的影响，而不会出现我遇到的错误?

Has anyone got any ideas on the best of getting my query safe from SQL injection without the errors that i'm experiencing?

更新

我现在有了它，所以它可以在没有参数的情况下工作，这是我更新的源代码，知道为什么不添加参数值吗?

I now have it so it work without the parameters bit here's my updated source code any idea why it won't add the parameter value?

Dim conn As SqlConnection = New SqlConnection("server='server1'; user id='w'; password='w'; database='w'; pooling='false'")
   conn.Open()


Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = @investor ') ", conn)
query.Parameters.AddWithValue("@investor", 69836)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

它是这样工作的

Dim conn As SqlConnection = New SqlConnection("server='server1'; user id='w'; password='w'; database='w'; pooling='false'")
   conn.Open()


Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = 69836') ", conn)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

我得到的错误是这个

An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'. 

这是因为它没有将 @investor 替换为 69836

And it's because it isn't replacing the @investor with the 69836

有什么想法吗?

解决方案

这是我如何解决我的问题

Here is how I solved my problem

Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'")

conn.Open()

Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

现在我可以编写查询而无需担心 SQL 注入

Now I can write queries without the worry of SQL injection

推荐答案

尝试使用 参数化查询 这里是一个链接 http://www.aspnet101.com/2007/03/parameterized-queries-in-asp-net/

Try using a parameterized query here is a link http://www.aspnet101.com/2007/03/parameterized-queries-in-asp-net/

另外，不要使用 OpenQuery...使用 this 来运行选择

Also, do not use OpenQuery... use the this to run the select

SELECT * FROM db...table WHERE ref = @ref AND bookno = @bookno

<小时>

描述您的一些选项的更多文章:

http://support.microsoft.com/kb/314520

什么是 T-连接到另一个 SQL Server 的 SQL 语法?

已编辑

注意:您最初的问题是询问分布式查询和链接服务器.这个新语句不引用分布式查询.我只能假设您现在直接连接到数据库.这是一个应该有效的示例.这是使用 SqlCommand.Parameters 的另一个参考站点

Note: Your original question was asking about distributed queries and Linked servers. This new statement does not reference a distributed query. I can only assume you are directly connecting to the database now. Here is an example that should work. Here is another reference site for using SqlCommand.Parameters

SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con); 
cmd.Parameters.Add("@ref", SqlDbType.Int);
cmd.Parameters["@ref"] = 34;

<小时>

已

好的，杰米泰勒，我会再次尝试回答你的问题.

Ok Jamie taylor I will try to answer your question again.

您正在使用 OpenQuery，因为您可能使用的是链接数据库

You are using OpenQuery becuase you are probably using a linked DB

基本上问题在于 OpenQuery 方法接受一个字符串，您不能将变量作为发送给 OpenQuery 的字符串的一部分进行传递.

Basically the problem is the OpenQuery Method takes a string you cannot pass a variable as part of the string you sent to OpenQuery.

您可以改为这样设置查询格式.该符号遵循 servername.databasename.schemaname.tablename.如果您通过 odbc 使用链接服务器，则省略 databasename 和 schemaname，如下图所示

You can format your query like this instead. The notation follows servername.databasename.schemaname.tablename. If you are using a linked server via odbc then omit databasename and schemaname, as illustrated below

    Dim conn As SqlConnection = New SqlConnection("your SQL Connection String")
    Dim cmd As SqlCommand = conn.CreateCommand()
    cmd.CommandText = "Select * db...table where investor = @investor"
    Dim parameter As SqlParameter = cmd.CreateParameter()
    parameter.DbType = SqlDbType.Int
    parameter.ParameterName = "@investor"
    parameter.Direction = ParameterDirection.Input
    parameter.Value = 34

这篇关于在 ASP.Net 中防止 SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！