Question 21006 in Mysql Question posted in
The official MySQL documentation can be found here.

Data format updating table in MySql from C#

I am trying to modify a table data using a SQL statement

foreach (Words words in Words_DB.Records)
{
   string _IPAUS = words.IPAUS;
   string _IPAUK = words.IPAUK;
   query = "UPDATE Words SET IPAUK='" + _IPAUK + "',IPAUS='" + _IPAUS + "' WHERE WORD='" + words.Word + "'";
   command.Parameters.Clear();
   command.CommandText = query;
   //command.Parameters.AddWithValue("@IPAUK", _IPAUK);
   //command.Parameters.AddWithValue("@IPAUS", _IPAUS);
   //command.Parameters.AddWithValue("@WORD", words.Word);
   int a = command.ExecuteNonQuery();
}

A example of query is UPDATE Words SET IPAUK=’ɑːd.vɑːk’,IPAUS=’ɑːrd.vɑːrk’ WHERE WORD=’aardvark’
The problem is when a read the database data I receive :

enter image description here

But, when I use the MySql Tools to execute the Query the result is right.
What I am doing wrong?

Regards

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks a lot for your helps. This is my final code working properly.

    string query = "UPDATE Words SET IPAUK=@IPAUK,IPAUS=@IPAUS WHERE WORD=@WORD";
var command = DatabaseConnection.MySql_Connection.CreateCommand();
try
{
  foreach (Words words in Words_DB.Records)
  {
     MySqlParameter IPAUSp = new MySqlParameter("@IPAUS", MySqlDbType.VarChar, 60);
     MySqlParameter IPAUKp = new MySqlParameter("@IPAUK", MySqlDbType.VarChar, 60);
     MySqlParameter WORD = new MySqlParameter("@WORD", MySqlDbType.VarChar, 50);
     command.Parameters.Clear();
     command.CommandText = query;
     command.Parameters.AddWithValue(IPAUKp.ToString(), words.IPAUK);
     command.Parameters.AddWithValue(IPAUSp.ToString(), words.IPAUS);
     command.Parameters.AddWithValue(WORD.ToString(), words.Word);
     int a = command.ExecuteNonQuery();
  }

    }


  2. 0

    The question concatenates raw input to generate a SQL query which exposes to SQL injection and bugs like this one. If _IPAUK contained '; -- all the data in that column would be lost.

    In this case it seems the code is trying to pass Unicode data using ASCII syntax, resulting in mangled data.

    The solution to both SQL injection and conversion issues is to use parameterized queries. In a parameterized query, the actual parameter values never become part of the query itself. The server compiles the SQL query into an execution plan and executes that using the parameter values.

    await using var connection = new MySqlConnection(connString);
await connection.OpenAsync();

// Insert some data
using (var cmd = new MySqlCommand())
{
    cmd.Connection = connection;
    cmd.CommandText = "UPDATE Words SET IPAUK=@IPAUK,IPAUS=@IPAUS WHERE WORD=@Word";
    cmd.Parameters.AddWithValue("IPAUK", words.IPAUK);
    cmd.Parameters.AddWithValue("IPAUS", words.IPAUS);
    cmd.Parameters.AddWithValue("Word", words.Word);
    await cmd.ExecuteNonQueryAsync();
}

    The example uses the open source MySQLConnector ADO.NET Driver instead of Oracle’s somewhat … buggy driver.

    The code can be simplified even more by using Dapper to construct the command, parameters and handle the connection automagically. Assuming words only has the IPAUK, IPAUS and Word properties, the code can be reduced to three lines :

    var sql="UPDATE Words SET IPAUK=@IPAUK,IPAUS=@IPAUS WHERE WORD=@Word";
await using var connection = new MySqlConnection(connString);
await connection.ExecuteAsync(sql,words);

    Dapper will construct a MySqlCommand, add parameters based on the properties of the parameter object (words), open the connection, execute the command and then close the connection

    Login or Signup to reply.
  3. 0

    Try it like this:

    command.CommandText = "UPDATE Words SET IPAUK= @IPAUK, IPAUS= @IPAUS WHERE WORD= @Word;";

// Match these to the column type and length in the DB
command.Parameters.Add("@IPAUK", MySQlDbType.VarChar, 30);
command.Parameters.Add("@IPAUS", MySQlDbType.VarChar, 30);
command.Parameters.Add("@Word", MySQlDbType.VarChar, 30);

foreach (Words words in Words_DB.Records)
{
    command.Parameters["@IPAUK"].Value = words.IPAUK;
    command.Parameters["@IPAUS"].Value = words.IPAUS;
    command.Parameters["@Word"].Value = words.Word;
    command.ExecuteNonQuery();
}

    Notice how the above minimizes the work done in the loop, which should improve performance, while also fixing the HUGE GAPING SECURITY ISSUE in the question from using string concatenation to build the query.

    Separately, I have the impression Words_DB.Records is the result of a prior query. It’s highly likely you could eliminate this entire section completely by updating the prior query to also do the update in one operation on the server. Not only would that greatly reduce your code, it will likely improve performance here by multiple orders of magnitude.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search