ASP.NET，C＃如何通过一个StringQuery到自定义SQL命令 [英] ASP.NET, C# How to Pass a StringQuery to a custom SQL Command

问题描述

我有一个轻微的问题，我有一个ASP.NET Web表单应用程序。我送过 URL？ID = X 是 X 是我的数据库索引或ID。

I have a slight issue, I have a ASP.NET Webforms application. I'm sending over a url?id=X were X is my database index or id.

我有一个C＃类文件来运行我的SQL连接和查询。这里是code：

I have a C# class file to run my SQL connection and query. Here is the code:

public DataTable ViewProduct(string id)
{
    try
    {
        string cmdStr = "SELECT * Products WHERE Idx_ProductId = " + id;

        DBOps dbops = new DBOps();
        DataTable vpTbl = dbops.RetrieveTable(cmdStr, ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString);
        return vpTbl;
    }
    catch (Exception e)
    {
        return null;
    }
}

因此​​，大家可以看到我的问题出在字符串cmdStr =SQL查询+变量;

我越过我的索引或ID通过URL请求，然后它，把它变成一个字符串，然后用 ViewProduct（pr​​oductId参数）。

I'm passing over my index or id through the URL then requesting it and turning it into a string then using ViewProduct(productId).

我不知道是什么语法，或者如何将ID添加到我的C＃字符串的SQL查询。我试过：

I don't know what syntax or how to add the id into my C# string sql query. I've tried:

string cmdStr = "SELECT * Products WHERE Idx_ProductId = @0" + id;
string cmdStr = "SELECT * Products WHERE Idx_ProductId = {0}" + id;

也是我目前有没有效果。

also what I have currently to no avail.

推荐答案

我非常确信这将是大约在C＃中参数化查询的一些典型问题重复，但显然没有一个（请参阅this)!

I was so sure this would be a duplicate of some canonical question about parameterized queries in C#, but apparently there isn't one (see this)!

您应该参数化查询 - 如果你不这样做，你运行本身注入查询恶意件code的风险。例如，如果你目前的code可以对数据库运行，这将是微不足道使该code做这样的事情：

You should parameterize your query - if you don't, you run the risk of a malicious piece of code injecting itself into your query. For example, if your current code could run against the database, it would be trivial to make that code do something like this:

// string id = "1 OR 1=1"
"SELECT * Products WHERE Idx_ProductId = 1 OR 1=1" // will return all product rows
// string id = "NULL; SELECT * FROM UserPasswords" - return contents of another table
// string id = "NULL; DROP TABLE Products" - uh oh
// etc....

ADO.NET提供了非常简单的功能参数化查询，你的 DBOps 类最稳妥不使用它（你传递一个建立命令字符串） 。相反，你应该做这样的事情：

ADO.NET provides very simple functionality to parameterize your queries, and your DBOps class most assuredly is not using it (you're passing in a built up command string). Instead you should do something like this:

public DataTable ViewProduct(string id)
{
    try
    {
        string connStr = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                // @id is very important here!
                // this should really be refactored - SELECT * is a bad idea
                // someone might add or remove a column you expect, or change the order of columns at some point
                cmd.CommandText = "SELECT * Products WHERE Idx_ProductId = @id";
                // this will properly escape/prevent malicious versions of id
                // use the correct type - if it's int, SqlDbType.Int, etc.
                cmd.Parameters.Add("@id", SqlDbType.Varchar).Value = id;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    DataTable vpTbl = new DataTable();
                    vpTbl.Load(reader);
                    return vpTbl;
                }
            }
        }
    }
    catch (Exception e)
    {
        // do some meaningful logging, possibly "throw;" exception - don't just return null!
        // callers won't know why null got returned - because there are no rows? because the connection couldn't be made to the database? because of something else?
    }
}

现在，如果有人试图通过NULL; SELECT * FROM SensitiveData，这将是正确的参数化。 ADO.NET/Sql Server将其转换为：

Now, if someone tries to pass "NULL; SELECT * FROM SensitiveData", it will be properly parameterized. ADO.NET/Sql Server will convert this to:

DECLARE @id VARCHAR(100) = 'NULL; SELECT * FROM SensitiveData';
SELECT * FROM PRoducts WHERE Idx_ProductId = @id;

这将返回任何结果（除非你有一个 Idx_ProductId 这实际上是字符串），而不是返回第二个 SELECT <结果/ code>。

which will return no results (unless you have a Idx_ProductId that actually is that string) instead of returning the results of the second SELECT.

一些额外的阅读：

• http://security.stackexchange.com/questions/25684/how-can-i-explain-sql-injection-without-technical-jargon


• Difference Parameters.Add和Parameters.AddWithValue
之间
在INSERT
• SQL注入


• 避免SQL注入不带参数


• How我创建一个参数化的SQL查询？为什么要我？（VB.NET）


• 我如何prevent SQL注入在PHP？（PHP具体，但很多有用的点）


• Is有一个规范的疑问告诉人们为什么他们应该使用SQL参数？

• http://security.stackexchange.com/questions/25684/how-can-i-explain-sql-injection-without-technical-jargon
• Difference between Parameters.Add and Parameters.AddWithValue
• SQL injection on INSERT
• Avoiding SQL injection without parameters
• How do I create a parameterized SQL query? Why Should I? (VB.NET)
• How can I prevent SQL-injection in PHP? (PHP specific, but many helpful points)
• Is there a canonical question telling people why they should use SQL parameters?

这篇关于ASP.NET，C＃如何通过一个StringQuery到自定义SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！