8

I know I can use the parameters, but what is the right way to escape string sequences? The query could be like this:

"INSERT INTO records (ReferenceID,Name,Note,Author) VALUES ('" + ID+ "','" + addlevel.textBox1.Text + "','"+addlevel.textBox2_note.Text+ "','"+Program.Username+"')";

I am ONLY curious, just want to know :)

EDIT: But what about that? "CREATE TABLE "+string" .... parameters cannot be used here!

Improve this question
1
  • When defining an identifier (e.g. a table name), it can't be escaped. You can put backticks around the identifier to allow some more characters (e.g. spaces), but you just have to make sure that the string doesn't contain anything harmful. Commented Jan 7, 2010 at 14:21

5 Answers 5

Reset to default
16

If you need to perform database operations, such as creating tables, then you should use SQL Server Management Objects instead of executing SQL strings.

For CRUD operations parameters is absolutely the only true path.

UPDATE: It appears that the MySQL client library contains a helper method for this ill-advised task. You can call MySqlHelper.EscapeString(string).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sorry, I didn't notice the tag. I don't know of a similar library fro MySQL.
An additional voice for using bound parameters - but if you're, say, transcoding some old scripts that use mysql into C# and just need a quick and dirty conversion then this is a handy quick-fix until you can get around to rewriting those database calls in a better way.
15

The right way is to use parameters.

"Just Say No" to trying to do the escaping yourself - it's far too easy to get wrong. Why do you think you'd want to escape them manually instead of using parameters?

Improve this answer

5 Comments

I know, I am jus curious. BTW, I have edited the question. What if user enters table name? Then I cannot use parameters either.
You wouldn't want to let users define the table they are inserting to, that would lead to a very large security hole.
There are use cases where the query must be generated and saved without being executed, for example, if you need to convert a datafile into an SQL script which can later be executed on the mysql server. However if you are directly interacting with the database, then parameters do make sense.
@fabspro: My experience is that most databases have a way of creating a text file dump of a database. If I needed a replay script or something like that, I'd prefer to ask the database to generate it than to try to do it myself. I trust them more than I trust myself :)
@JonSkeet I absolutely agree with you, although there can be situations where the alternative can be easier. For example, last night I wrote a program to generate sql queries to insert data from a series of separate excel spreadsheets (which were related tables from an rdbms). I'm sure there are excel to postgresql conversion programs out there, but I found that directly generating inline SQL was easier (because the database was a remote server running linux). Maybe I should have investigated excel reading libraries so I could have run the conversion on the server with the files directly...
8

If you really, really, really need to do the escaping yourself (of which there is no sign in your example):

string EncodeMySqlString(string value) {
   return value.Replace(@"\", @"\\").Replace("'", @"\'")
}
Improve this answer

2 Comments

@erikkallen: No, it's not for MS SQL Server. Please check what the question is about before voting down...
Thanks. I have a one-off task that required a quick-and-dirty dump of some data to a sql script that I'll be running directly and have no future use of. This was all I needed.
0

I think the only thing you need to do is value = value.Replace("'", "''")

Of course you shouldn't do this, but you know that.

Edit: Apparantly this is all wrong for MySQL. It should work for PostgreSQL, MS SQL and Oracle, though.

Improve this answer

1 Comment

Notice that this is for MySQL, not MS SQL Server. The method that you suggested is ABSOLUTELY NOT sufficient for escaping strings for MySQL.
0

use commnd parameters instead. It takes care of escaping itself. It's the solution also against sql injections.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.