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(Proj ectID, 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]]

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

Bill,

"Bill Gower" <billgower@char ter.netschrieb:

>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(Proj ectID, 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.Par ameters' property.

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

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

[RobinS]

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

Use parameters instead.

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

Dim cn As New SqlConnection(c onnString)
cn.Open()
Dim cmd As New SqlCommand(mySQ L, cn)
'create the new parameter
cmd.Parameters. AddWithValue("@ ProjectID", locProjectID)
cmd.Parameters. AddWithValue("@ SQLID", nID)
cmd.Parameters. AddWithValue("@ SQLString", tSQL)
cmd.ExecuteNonQ uery()
cn.Close()


Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
"Bill Gower" <billgower@char ter.netwrote in message
news:%23VR3MhtS HHA.3500@TK2MSF TNGP05.phx.gbl. ..

>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(Proj ectID, 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
>
>

[Tiago Salgado]

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

Try somthing like this:

cmd = new SqlCommand("INS ERT INTO ProjectSQL(Proj ectID,SqlID,Sql String)
VALUES(@ProjID, @SqlID,@SqlStri ng",yourSqlConn ection)
cmd.Parameters. Add("@ProjID",S qlDbType.Int).V alue = locProjectID
cmd.Parameters. Add("@SqlID",Sq lDbType.Int).Va lue = 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

"Bill Gower" <billgower@char ter.netwrote in message
news:%23VR3MhtS HHA.3500@TK2MSF TNGP05.phx.gbl. ..

>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(Proj ectID, 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 Gower]

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

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

"RobinS" <RobinS@NoSpam. yah.nonewrote in message
news:Bfednbev8f C7l1fYnZ2dnUVZ_ t-mnZ2d@comcast.c om...

Use parameters instead.
>
Dim mySQL As String = _
"INSERT INTO ProjectSQL (ProjectID, SQLID, SQLString) " & _
" VALUES @ProjectID, @SQLID, @SQLString "
>
Dim cn As New SqlConnection(c onnString)
cn.Open()
Dim cmd As New SqlCommand(mySQ L, cn)
'create the new parameter
cmd.Parameters. AddWithValue("@ ProjectID", locProjectID)
cmd.Parameters. AddWithValue("@ SQLID", nID)
cmd.Parameters. AddWithValue("@ SQLString", tSQL)
cmd.ExecuteNonQ uery()
cn.Close()
>
>
Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
"Bill Gower" <billgower@char ter.netwrote in message
news:%23VR3MhtS HHA.3500@TK2MSF TNGP05.phx.gbl. ..

>>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(Proj ectID, 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
>>
>>

>
>

[lord.zoltar@gmail.com]

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

On Feb 7, 11:52 am, "Bill Gower" <billgo...@char ter.netwrote:

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(Proj ectID, 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]]

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

"Bill Gower" <billgower@char ter.netschrieb:

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.publ ic.vb.*" hierarchy.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

[RobinS]

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

Try posting this to microsoft.publi c.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.
-----------------------------------------------
"Bill Gower" <billgower@char ter.netwrote in message
news:%23qeT1huS HHA.496@TK2MSFT NGP06.phx.gbl.. .

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
>
"RobinS" <RobinS@NoSpam. yah.nonewrote in message
news:Bfednbev8f C7l1fYnZ2dnUVZ_ t-mnZ2d@comcast.c om...

>Use parameters instead.
>>
>Dim mySQL As String = _
> "INSERT INTO ProjectSQL (ProjectID, SQLID, SQLString) " & _
> " VALUES @ProjectID, @SQLID, @SQLString "
>>
>Dim cn As New SqlConnection(c onnString)
>cn.Open()
>Dim cmd As New SqlCommand(mySQ L, cn)
>'create the new parameter
>cmd.Parameters .AddWithValue(" @ProjectID", locProjectID)
>cmd.Parameters .AddWithValue(" @SQLID", nID)
>cmd.Parameters .AddWithValue(" @SQLString", tSQL)
>cmd.ExecuteNon Query()
>cn.Close()
>>
>>
>Robin S.
>Ts'i mahnu uterna ot twan ot geifur hingts uto.
>-----------------------------------------------
>"Bill Gower" <billgower@char ter.netwrote in message
>news:%23VR3Mht SHHA.3500@TK2MS FTNGP05.phx.gbl ...

>>>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(Proj ectID, SQLID, SQLString) values( " &
>>locProjectI D & ", " & 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
>>>
>>>

>>
>>

>
>

[Jim Wooley (MVP)]

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

Hello lord.zoltar@gma il.com,

On Feb 7, 11:52 am, "Bill Gower" <billgo...@char ter.netwrote:
>

>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(Proj ectID, SQLID, SQLString) values( " &
>locProjectID & ", " & nID & ", '" & tSQL & "')" that I will use
>inside of VB
>>
>>

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/jwooley