Data format updating table in MySql from C#-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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

}

CodePudding user response：

the more important issue is that your code is vulnerable to SQL injecting attacks as you are concatenating row string values into the query. this allows the attackers to execute arbitrary SQL code by injecting malicious string into the parameters.

to avoid this issue you should use parameterized queries instead. this will ensure that any special characters in the parameters are properly escaped and treated as data, not part of the SQL code.

but remember, the standard way is to use "stored procedures", "views", "transactions", and ... (not raw queries)