带有SQL注入的全文搜索的ADO select语句

问题描述

我要连接的数据库有一个带有全文搜索索引的表.这可以正常工作.

The database that I am connecting to has a table with a Full Text Search index. This works correctly.

select * from MyTable where contains(*, 'value')

在WPF中，如果我向下发送该确切命令，它将起作用.但是value不是硬编码的，它是用户键入的内容，因此需要对其进行保护以进行SQL注入.问题在于这样做不会返回结果.这是我的代码；

In WPF if I send that exact command down it works. However value is not hard coded it is something an user types in so it needs to be protected for SQL injection. The issue is that in doing so it does not return results. Here is my code;

DataTable dt = new DataTable();

        string ConString = "Data Source=127.0.0.1,1433;Initial Catalog=MyDB;User Id=sa;Password=amazingSecurePassword;";

        using (SqlConnection con = new SqlConnection(ConString))
        {
            string sqlCMD = "select * from MyTable where contains(*, @s1)"
            SqlCommand cmd = new SqlCommand(sqlCMD, con);
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                con.Open();
                cmd = new SqlCommand(sqlCMD, con);
                cmd.Parameters.Add(new SqlParameter("@s1", "value"));

                da.SelectCommand = cmd;
                da.Fill(dt);
                con.Close();

            }
            catch (Exception x)
            {
                //Error logic
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

@Mike评论有效.更改SqlDbType.NVarChar解决了该问题

@Mike comment worked. Change the SqlDbType.NVarChar fixed the issue

推荐答案

如上面的注释所述，在创建SqlParameter期间将SQlDbType设置为NVarChar有助于CLR确定正确的数据类型.有关 MSDN上SqlParameter构造函数的更多信息.

As noted in the above comment, setting the SQlDbType to NVarChar during the creation of the SqlParameter helps the CLR determine the right data type. More info about the SqlParameter constructor at MSDN.

这篇关于带有SQL注入的全文搜索的ADO select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！