如何通过MySql用撇号传递C#变量 [英] How to pass a C# variable with apostrophe through MySql

问题描述

我在向数据库中输入变量时遇到问题.我看过其他文章，仅介绍了如何通过转义变量来传递变量，但是这些解决方案并不适用，因为我通过API获取变量.顺便说一下，我正在使用foreach循环遍历数据.

I'm having a problem inputting variables into my database. I've seen other posts on how to pass a variable through by just escaping it, but those solutions do not apply because I am getting my variable's through an API. I'm cycling though data with a foreach loop by the way.

level = "" + x.Account_Level + "";
name = "" + x.name + "";
command.CommandText = "INSERT INTO `data` (`level`, `name`) VALUES(" + level + ", " + name + ")";
command.ExecuteNonQuery();  

有时，变量会带有撇号，并且会弄乱代码.是否可以在每个撇号之前插入斜杠，或者是否有类似PHP的方法通过单引号将整个变量压入?谢谢！

Sometimes, a variable will come back with an apostrophe and will screw up the code. Is it possible to insert a slash before every apostrophe or is there a way like in PHP to just push the whole variable through with single quotes? Thanks!

修改: 这行得通吗?我想我需要在每个循环中添加i来更改参数的名称，因为它声明了已声明的参数.

Would this work? I think I need to add the i to change the name of the parameter each loop, due to it claiming the parameter as already declared.

using (var web = new WebClient())
        {
        web.Encoding = System.Text.Encoding.UTF8;
        var jsonString = responseFromServer;
        var jss = new JavaScriptSerializer();
        var MatchesList = jss.Deserialize<List<Matches>>(jsonString);
        string connectString = "Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;";
        MySqlConnection connect = new MySqlConnection(connectString);
        MySqlCommand command = connect.CreateCommand();
        int i = 1;


        connect.Open();
        foreach (Matches x in MatchesList)
        {

            command.CommandText = "INSERT INTO `data` (`level`, `name`) VALUES(?level" + i + ", ?name" + i + ")";

             command.Parameters.AddWithValue("level" + i, x.Account_Level);
             command.Parameters.AddWithValue("mode" + i, x.name);

            command.ExecuteNonQuery();
            i++;
        }
        connect.Close();                    
    }

推荐答案

快速而肮脏的解决方法是使用类似以下内容的东西:

The quick and dirty fix is to use something like:

level = level.Replace("'","whatever");

但是仍然存在问题.它不会捕获其他不良字符，甚至对于单引号上的边缘情况也可能不起作用.

but there are still problems with that. It won't catch other bad characters and it probably won't even work for edge cases on the apostrophe.

最佳解决方案是不要以这种方式构造查询.相反，请学习如何使用参数化查询，以使不可能进行SQL注入攻击，并且无论您在参数中添加了什么，参数都可以正常工作(当然，这是有原因的).

The best solution is to not construct queries that way. Instead, learn how to use parameterised queries so that SQL injection attacks are impossible, and the parameters work no matter what you put in them (within reason, of course).

例如(在我的头顶上，因此可能需要一些调试):

For example (off the top of my head so may need some debugging):

MySqlCommand cmd = new MySqlCommand(
    "insert into data (level, name) values (?lvl, ?nm)", con);
cmd.Parameters.Add(new MySqlParameter("lvl", level));
cmd.Parameters.Add(new MySqlParameter("nm", name)); 
cmd.ExecuteNonQuery();

这篇关于如何通过MySql用撇号传递C#变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！