Escaping quotes in a string in VB6

Question

Hello,

I am trying to make some small changes to an old VB web app I need to add quotes inside of a string I've had no luck so far. The string is

Dim sql As String = "Select * from  Usertask Where UserId = " & Session("UserId") & " and JobID=" & ddlReqTask.SelectedValue

I need to add quotes around the Session("UserID") value.

Answer 1

You can use "" to insert a quote into a string e.g:

dim sometext as String = "Hello ""Frank"" how are you?"

Which gives you

Hello "Frank" how are you?

Answer 2

To escape a quote you just need to add another quote, I believe this is what you need:

Dim sql As String = "Select * from  Usertask Where UserId = """ & Session("UserId") & """ and JobID=" & ddlReqTask.SelectedValue

Answer 3

You could also use Chr(34) in the concatentation.

Dim sql As String = "Select * from  Usertask Where UserId = " & Chr(34) & Replace(Session("UserId"), Chr(34), Chr(34) & Chr(34)) & Chr(34) & " and JobID=" & CLng(ddlReqTask.SelectedValue)

Either way works (the other examples and this one). some people prefer this one as it can be less confusing, however the above examples arent perfectly ledgible and arent exatly rocket science

Answer 4

This is a SQL injection vulnerability and you should NOT be doing it. By doing it this way, you allow your users to execute any query they want to by giving you a UserId like

'; DROP TABLE Usertask; --

Instead, use parameters. Depending on how you are executing the SQL, there are different ways to do it; please show us the code that executes the SQL query.

---

In answer to your question,

Dim StringWithQuotes As String = "Hello, I've got ""Quotes""!"

This string will be

Hello, I've got "Quotes"!

Answer 5

Most SQL servers, in my experience, need a single quote for strings. The best way to do it is to let .net deside for you, by using SQL Parameters. Here's a sample (also in VB.Net): http://www.knowdotnet.com/articles/dynamicsqlparameters.html
This also has the benefit of security against SQL injections.

Answer 6

I'd recommend you use parameterised SQL instead of building up an adhoc SQL statement like this as you could leave yourself open to SQL injection. This means you don't need to worry about concatenating quotes into the string, as well as also improving query performance (assuming sql server) as it allows execution plan caching and reuse.

e.g.

Dim sql As String = "Select * from  Usertask Where UserId = ? AND JobID = ?"

Then add 2 ADODB.Parameters to the Command object to supply the values for the 2 parameters e.g.

Set param = New ADODB.Parameter
param.Name = "@UserId"
param.Direction = adParamInput
param.Type = adVarChar
param.Size = (give size of user id field)
param.value = Session("UserId")
yourADOCommand.Parameters.Append param

And the same again for the JobId parameter.