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

  • Thread starter Thread starter Bill Gower
  • Start date Start date
B

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
 
Bill,

Bill Gower said:
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.
Click to expand...


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>
 
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.
 
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
 
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
Click to expand...

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
 
Bill Gower said:
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.
Click to expand...

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.
 
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.
-----------------------------------------------
 
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
Click to expand...

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
 
Back
Top