从数据库返回某些值的安全方法(SQL SERVER&ASP.NET) [英] The secure way to return some value from database (SQL SERVER & ASP.NET)

问题描述

下面的代码是否实现了从数据库检索数据的安全方式?请帮助我，我不了解SQL注入.有人告诉我，此代码可以轻松注入.如果是，有人可以解释吗?谢谢.

Is the code below implementing the secure way to retrieve the data from database? help me please, I don't understand about SQL Injection. Someone told me this code can easily get injected. If yes, can somebody explain it? Thank you.

public int CheckID(string column, string table, string wheres)
    {
        int i = 0;
        sqlcon = ConnectToMain();
        string sqlquery = "SELECT "+column+" FROM "+table+" "+wheres+"";
        using (sqlcon)
        {
            sqlcon.Open();
            SqlCommand sqlcom = new SqlCommand(sqlquery, sqlcon);
            using (sqlcom)
            {
                SqlDataReader dr = sqlcom.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    i = dr.GetInt32(0);
                }
                else
                {
                    i = 0;
                }
            }
            sqlcon.Close();
        }
        return i;
    }

推荐答案

此代码有太多问题.

• 表，列和条件作为字符串传递并连接在一起，这意味着该代码易于进行SQL注入.
• 诸如表，列条件之类的数据库详细信息会泄漏到函数的调用程序中.您是否要使用此方法来查询除Visitor表以外的任何内容?
• 仅需要单个值时使用阅读器.
• 该连接是在 using 块之外创建的，并存储在字段中.这肯定是内存泄漏，也可能是连接泄漏.只需在本地创建连接即可.

• Table, column and criteria are passed as strings and concatenated, which means that the code is prone to SQL injection.
• Database details like table, column criteria are spilled into the function's caller. Are you going to use this method to query anything other than a Visitor table?
• A reader is used when only a single value is wanted.
• The connection is created outside the using block and stored in a field. This is definitelly a memory leak and probably a connection leak as well. Just create the connection locally.

一个简单的命令调用可以解决所有这些问题:

A simple command call fixes all of these problems:

public int CheckIDVisitor(visitorName)
{
    string query = "SELECT ID FROM Visitors where Name=@name";
    using (var sqlConn=new SqlConnection(Properties.Default.MyDbConnectionString))
    using( var cmd=new SqlCommand(query,sqlConn))
    {
        var cmdParam=cmd.Parameters.Add("@name",SqlDbType.NVarChar,20);
        cmdParam.Value=visitorName;
        sqlConn.Open();

        var result=(int?)cmd.ExecuteScalar();
        return result??0;
    }
}

您还可以预先创建命令并将其存储在字段中.您可以在每次执行命令时将连接附加到命令:

You could also create the command in advance and store it in a field. You can attach the connection to the command each time you want to execute it:

public void InitVisitorCommand()
{
    string query = "SELECT ID FROM Visitors where Name=@name";
    var cmd=new SqlCommand(query,sqlConn);
    var cmdParam=cmd.Parameters.Add("@name",SqlDbType.NVarChar,20);
    _myVisitorCommand=cmd;
}

...

public int CheckIDVisitor(visitorName)
{
    using (var sqlConn=new SqlConnection(Properties.Default.MyDbConnectionString))
    {
        _myVisitorCommand.Parameters.["@name"]Value=visitorName;
        _myVisitorCommand.Connection=sqlConn;
        sqlConn.Open();

        var result=(int?)cmd.ExecuteScalar();
        return result??0;
    }
}

一个更好的选择是使用诸如 Dapper.Net 删除所有这些代码:

An even better option would be to use a micro-ORM like Dapper.Net to get rid of all this code:

public int CheckIDVisitor(visitorName)
{
    using (var sqlConn=new SqlConnection(Properties.Default.MyDbConnectionString))
    {
        string sql = "SELECT ID FROM Visitors WHERE name=@name"
        var result = conn.Query<int?>(sql, new { name = visitorName);
        return result??0;
    }
}

或

public int[] CheckIDVisitors(string []visitors)
{
    using (var sqlConn=new SqlConnection(Properties.Default.MyDbConnectionString))
    {
        string sql = "SELECT ID FROM Visitors WHERE name IN @names"
        var results = conn.Query<int?>(sql, new { names = visitors);
        return results.ToArray();
    }
}

这篇关于从数据库返回某些值的安全方法(SQL SERVER&ASP.NET)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！