Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
357 views
in Technique[技术] by (71.8m points)

How to pass a C# variable with apostrophe through MySql

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

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!

Edit: 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();                    
    }
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...