Escape a string (add slashes) in VB.net?

问题:

Very simple question (surprisingly I can't find a similar question anywhere): how do I escape form data in VB.net? I have various lines like this:

Dim query As String = "exec sp_Message_insert @clientid='" + pClientId + "', @message='" + pMessage + "', @takenby='" + pUserId + "', @recipients='" + pRecipients + "'"

If I use an apostrophe in the message then of course this screws up the query. I've looked through the intellisense functions on the string but don't see anything appropriate...

回答1:

What exactly do you mean by escaping? VB.NET doesn't have 'escaping' in the same way that c-style languages do.

Now, if you want to ensure that there are no single-qoutes in the pClientId variable, then you have two options:

Option 1 (not recommended for this scenario): do a simple replace. I.e.

pClientId = String.Replace(pClientId, "'","''")

But, as noted, I would NOT do this for what appears to be a SQL Command. What I would do is Option 2: use data parameters to pass parameters to your DB during sql commands

For example:

Dim cn As New SqlConnection(connectionString)
Dim cmd As New SqlCommand
cn.Open
cmd.Connection=cn
cmd.CommandType=CommandType.StoredProcedure
cmd.CommandText= "sp_Message_insert"
cmd.Parameters.add(New SqlParameter("@clientid", pClientId)
cmd.Parameters.add(New SqlParameter("@message", pMessage)
cmd.Parameters.add(New SqlParameter("@takenby", pUserId)
cmd.Parameters.add(New SqlParameter("@recipients", pRecipients)
cmd.ExecuteNonQuery

回答2:

If you want to escape the strings then you first have to tell what database you are using. You have to use the correct escaping for the specific database so that you escape all the characters that you need to, but only those.

I don't know of any database that uses slash as escape character. MySQL uses backslashes, perhaps that is what you mean?

The best is not to escape the strings at all, but to use a parameterised query. Example:

Dim cmd As New SqlCommand("sp_Message_insert")
cmd.Parameters.Add("@clientid").Value = pClientId
cmd.Parameters.Add("@message").Value = pMessage
cmd.Parameters.Add("@takenby").Value = pUserId
cmd.Parameters.Add("@recipients").Value = pRecipients

回答3:

I think you can just do two apostrophes to create the one. I apologize if that does not work, it has been a while since I have done it that way, I would suggest using SQL Parameters, this will automatically handle your special characters and prevent SQL injection.

回答4:

Don't build up a string to execute like that.
That's exactly why SQL Injection attacks are possible.

Instead use a Data Access Layer, which lets you create parameter objects and associate them with the stored procedure to execute.

回答5:

if you want to execute a String as a query you should use the following code:

Dim query as String 
query.Replace("/", "//")

回答6:

So would like to add a small notice about parameters names using together with System.Data.Odbc.OdbcCommand, according to http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.commandtype

The .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder.

an example from here http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparametercollection(v=vs.80).aspx#Y800 :

Dim command As OdbcCommand = connection.CreateCommand()
command.CommandText = “{ call MoneyProcedure(?,?,?) ”

command.Parameters.Add("", OdbcType.Int).Value = 1
command.Parameters.Add("", OdbcType.Decimal).Value = 2
command.Parameters.Add("", OdbcType.Decimal).Value = 3