mysql命令少打冒号怎么处理_在C#中使用mySQL转义逗号,冒号和单引号

--- THIS IS FOR PERSONAL USE, SO DON'T WORRY ABOUT SQL INJECTION ---

I've been browsing through several tutorials on mySQL escaping for C# but cannot find one that works for me (maybe I'm just using it incorrectly)

I'm trying to insert data into a mySQL database.

Here's the code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Diagnostics;

using System.Net;

using System.IO;

using System.Security.Cryptography;

using MySql.Data;

using MySql.Data.MySqlClient;

namespace HASHSITE

{

class Program

{

static void Main(string[] args)

{

bool success;

int done = 0;

string path = @"C:\Users\somePC\Documents\someFolder\somefile.txt";

string server = "someIP";

string database = "some_db";

string uid = "some_dbu";

string password = "pass";

string connectionstring = "SERVER=" + server + ";DATABASE=" + database + ";UID=" + uid + ";PASSWORD=" + password + ";";

using (var connection = new MySqlConnection(connectionstring))

{

connection.Open();

using (var cmd = new MySqlCommand("INSERT INTO databases(data) VALUES(@name)", connection))

{

var parameter = cmd.Parameters.Add("@name", MySqlDbType.LongText);

foreach(string line in File.ReadLines(path))

{

success = false;

while (!success)

{

parameter.Value = line;

cmd.ExecuteNonQuery(); //ERROR IS HERE

success = true;

}

done += 1;

Console.WriteLine("\n" + done);

}

}

}

}

}

}

I need to escape commas present in the string line which is

name,name@name.com

ERROR:

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'databases(data) VALUES

Talk1:

Writing code like this is first step to SQL Injection. Use parameter binding.

Talk2:

Hey @lad2025 I am using it personally to upload some data. It won't be distributed, so SQL injection is not a problem for now

Talk3:

You create problem because you concatenate string. Just bind parameter and you don't need to escape it.

Talk4:

Can you please give me the code to that?

Talk5:

Just search string.Format

Solutions1

Don't try to escape anything - just use parameterized SQL. You may not care about SQL injection attacks now, but you will at some point... and by using parameterized SQL for your values, you'll remove both the escaping and injection attack issues. Oh, and you'll reduce type conversion concerns. And make your SQL more readable. It's a win all round.

Note that DATABASES is a reserved word, so you do need to quote that - or change the name of your table to something which isn't going to be as awkward.

// TODO: Specify the column name as well, for clarity if nothing else

cmd.CommandText = "INSERT INTO 'databases' VALUES(@name)";

// Adjust for your actual type

cmd.Parameters.Add("@name", MySqlDbType.LongText).Value = line;

...

Note that you only want to call cmd.Parameters.Add once though, so in your case with a loop you'd probably want to call that (and set the command text) outside the loop, then just set the Value property inside the loop.

When in doubt, get in the habit of doing the right thing early, rather than assuming you'll find the time to undo your bad habits later on.

While you're at it, now would be a good time to start using using statements... and you can reuse pretty much everything, just changing the parameter value on each iteration:

using (var connection = new MySqlConnection(...))

{

connection.Open();

using (var command = new MySqlCommand("INSERT INTO 'databases' VALUES(@name)", connection)

{

var parameter = command.Parameters.Add("@name", MySqlDbType.LongText);

foreach (...)

{

parameter.Value = line;

command.ExecuteNonQuery();

}

}

}

Talk1:

I would add also to specify column name explicitly INSERT INTO databases(column_name) VALUES ... as a part of good practices.

Talk2:

Thank you for the suggestion. i am stuck on the same error: An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'databases VALUES('TheUnexpected,danepullen@yahoo.com,Graffiti')' at line 1 Occuring at: cmd.ExecuteNonQuery();

Talk3:

Jon, isn't that weird error message on command = "INSERT INTO databases VALUES(" + line + ")" line? I mean, it is just a string concatenation. If sql query needs to be escaped, error message wouldn't be on ExecuteNonQuery line instead?

Talk4:

önül: I suspect that's a diagnostic error, personally.

Talk5:

MY FAULT, error messagfe is on executenonquery, but it is because of my incorrect statement. My apologies.