在ASP.Net preventing SQL注入 [英] Preventing SQL Injection in ASP.Net

问题描述

我有这样的code

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

我将如何prevent这个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命令下面提到。

的错误是这样

'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.

更新NO 2

我在code加入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?

更新

我现在有它，所以它不工作的参数位，这里是我更新的源$ C ​​$ C任何想法，为什么它不会添加参数值？

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()

它的工作原理是这样

It works like this

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()

我得到的错误是这样

The error i'm getting is this

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

任何想法？

SOLUTION

下面是我如何解决我的问题。

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 ......用它来运行的选择

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

<一个href=\"http://stackoverflow.com/questions/125457/what-is-the-t-sql-syntax-to-connect-to-another-sql-server\">What是T-SQL语法连接到另一个SQL Server？

编辑

请注意：您原来的问题是问有关分布式查询和链接服务器。这个新的说法不引用分布式查询。我只能假设你是直接连接到数据库了。下面是应该工作的例子。
下面是使用 SqlCommand.Parameters <另一个参考网站/ A>

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.

您是那位你可能使用链接的DB使用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链接的服务器，然后忽略数据库名称和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 preventing SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！