Problem with inserting a string with quotes into a table from VB

[Bill Gower]

I want to store an SQL string into a table and then be able to run it later.

Here is the insert command

"insert into ProjectSQL(ProjectID, SQLID, SQLString) values( " &
locProjectID & ", " & nID & ", '" & tSQL & "')" that I will use inside of VB

The problem is that inside the string tSQL are single quoted values so I
need double quotes around the tSQL when it is stored in the table.

What do I need to do to achieve this?

Bill

 

[Herfried K. Wagner [MVP]]

Bill,

I want to store an SQL string into a table and then be able to run it
later.

Here is the insert command

"insert into ProjectSQL(ProjectID, SQLID, SQLString) values( " &
locProjectID & ", " & nID & ", '" & tSQL & "')" that I will use inside of
VB

The problem is that inside the string tSQL are single quoted values so I
need double quotes around the tSQL when it is stored in the table.

I strongly recommend to use a parameterized command object instead of
building the SQL command string using string concatenations in order to
prevent SQL injection. You will find a sample in the documentation for the
'SqlCommand.Parameters' property.

ADO.NET Secure Coding Guidelines
<URL:http://msdn2.microsoft.com/en-us/hdb58b2f.aspx>

 

[Tiago Salgado]

Try somthing like this:

cmd = new SqlCommand("INSERT INTO ProjectSQL(ProjectID,SqlID,SqlString)
VALUES(@ProjID,@SqlID,@SqlString",yourSqlConnection)
cmd.Parameters.Add("@ProjID",SqlDbType.Int).Value = locProjectID
cmd.Parameters.Add("@SqlID",SqlDbType.Int).Value = nID
cmd.Parameters.Add("@SqlString",SqlDbType.Int).Value = tSQL


--

Tiago Salgado

http://weblogs.pontonetpt.com/tiagosalgado
http://www.foruns.org
http://www.portugal-a-programar.org
http://www.revista-programar.info

 

[RobinS]

Use parameters instead.

Dim mySQL As String = _
"INSERT INTO ProjectSQL (ProjectID, SQLID, SQLString) " & _
" VALUES @ProjectID, @SQLID, @SQLString "

Dim cn As New SqlConnection(connString)
cn.Open()
Dim cmd As New SqlCommand(mySQL, cn)
'create the new parameter
cmd.Parameters.AddWithValue("@ProjectID", locProjectID)
cmd.Parameters.AddWithValue("@SQLID", nID)
cmd.Parameters.AddWithValue("@SQLString", tSQL)
cmd.ExecuteNonQuery()
cn.Close()


Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[Bill Gower]

Unfortunately I have inherited an VB 6 that I am trying to support. I will
be upgrading it to .net this year but for now I just have to maintain the
system.

Bill

 

[lord.zoltar]

I want to store an SQL string into a table and then be able to run it later.

Here is the insert command

"insert into ProjectSQL(ProjectID, SQLID, SQLString) values( " &
locProjectID & ", " & nID & ", '" & tSQL & "')" that I will use inside of VB

The problem is that inside the string tSQL are single quoted values so I
need double quotes around the tSQL when it is stored in the table.

What do I need to do to achieve this?

Bill

You could use Replace to replace all occurences of ' with '' (which is
two single quotes).
Also it might be useful for you to read this article on SQL Injection:
http://msdn2.microsoft.com/en-us/library/ms161953.aspx

 

[Herfried K. Wagner [MVP]]

Unfortunately I have inherited an VB 6 that I am trying to support. I
will be upgrading it to .net this year but for now I just have to maintain
the system.

Well, then why are you asking the question in a VB.NET group ;-)? The
Classic VB groups can be found in the "microsoft.public.vb.*" hierarchy.

 

[RobinS]

Try posting this to microsoft.public.vb or comp.lang.basic.visual.misc.
Those are VB6 groups. This is a vb.Net group.

Thanks,
Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------

 

[Jim Wooley (MVP)]

Hello (e-mail address removed),

You could use Replace to replace all occurences of ' with '' (which is
two single quotes).
Also it might be useful for you to read this article on SQL Injection:
http://msdn2.microsoft.com/en-us/library/ms161953.aspx

I second the recommendation on the SQL Injection issue. It is not specific
to .Net and your VB6 app is just as vulnerable. Use parameterized queries
instead of string concatenation when dealing with the database. Here you
kill two birds with one stone as you can pass a value with double quotes
in as a parameter value without issue as well as avoiding SQL Injection vulnerabilities.

Jim Wooley
http://devauthority.com/blogs/jwoole