[ACCEPTED]-Escape a string (add slashes) in VB.net?-escaping

Accepted answer
Score: 16

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

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

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

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

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

For example:

Dim cn As New SqlConnection(connectionString)
Dim cmd As New SqlCommand
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)
Score: 5

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

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

The best is not to escape the strings 1 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
Score: 2

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

Score: 2

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

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

Score: 1

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

Dim query as String 
query.Replace("/", "//")
Score: 1

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

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

an 1 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

More Related questions