query delimiters

[Jim]

I have a query and am inserting fields into a table. Works usually. I
delimit the fields with single quotes ('). The problem is some fields
(row(0) or row(1)) have single quotes. What to do?

Dim strSQL As String = "INSERT INTO tbl_All_X12 (field1, field2) " & _
"values ('" & row(0) & "','" & row(1) & "');"

[Spam Catcher]

Re: query delimiters

Jim <joSmith2im@Rem oveThisStuffNet scape.netwrote in
news:ONGdnYn7fr aNpzvbnZ2dnUVZ_ tDinZ2d@massill oncabletv.com:

I have a query and am inserting fields into a table. Works usually. I
delimit the fields with single quotes ('). The problem is some fields
(row(0) or row(1)) have single quotes. What to do?

Don't concatante SQL strings. Use SQL paramters.

[Jim]

Re: query delimiters

That did it. thanks. I'm a newbe at this and I just sniped the code
out of something I found that worked.

Spam Catcher wrote:

Jim <joSmith2im@Rem oveThisStuffNet scape.netwrote in
news:ONGdnYn7fr aNpzvbnZ2dnUVZ_ tDinZ2d@massill oncabletv.com:
>

>I have a query and am inserting fields into a table. Works usually. I
>delimit the fields with single quotes ('). The problem is some fields
>(row(0) or row(1)) have single quotes. What to do?

>
>
Don't concatante SQL strings. Use SQL paramters.

[Spam Catcher]

Re: query delimiters

Jim <joSmith2im@Rem oveThisStuffNet scape.netwrote in news:is-
dneOzk5RFzjvbnZ 2dnUVZ_sDinZ2d@ ma...ncablet v.com:

That did it. thanks. I'm a newbe at this and I just sniped the code
out of something I found that worked.

FYI, the reason why I suggested not to use concatenation is because it
exposes your code to SQL injection. As you noticed, escape characters such
as single quotes (') can cause your SQL code to bomb out. A hacker could
mangle the sql string with escape characters and run unexpected SQL (i.e.
DELETE FROM TABLE) ;-)

[Tony M]

Re: query delimiters

I don't understand and I know I'm wrong but...

I could have
SQL = "select * from MyDB where userid = " & chr(39) & textboxuserID &
chr(39)
Select * from MyDB where userid = 'Tony'

now if i type into the textbox

Tony and userid like a%

Wouldn't translate to

Select * from MyDB where userid = 'Tony and userid like a%'

It just looks like a bad userid to me.

No matter what I type into the textbox it will be surrounded by single
quotes.

Numeric values are different and I can see how it might work.


Tony





"Spam Catcher" <spamhoneypot@r ogers.comwrote in message
news:Xns99779F9 A01C6Fusenethon eypotrogers@127 .0.0.1...

Jim <joSmith2im@Rem oveThisStuffNet scape.netwrote in news:is-
dneOzk5RFzjvbnZ 2dnUVZ_sDinZ2d@ ma...ncablet v.com:
>

>That did it. thanks. I'm a newbe at this and I just sniped the code
>out of something I found that worked.

>
>
FYI, the reason why I suggested not to use concatenation is because it
exposes your code to SQL injection. As you noticed, escape characters such
as single quotes (') can cause your SQL code to bomb out. A hacker could
mangle the sql string with escape characters and run unexpected SQL (i.e.
DELETE FROM TABLE) ;-)

[Jack Jackson]

Re: query delimiters

Type into the textbox:

a' delete from MyDB

On Tue, 24 Jul 2007 19:55:08 -0400, "Tony M" <TonyMast_NOSPA M@msn.com>
wrote:

>I don't understand and I know I'm wrong but...
>
>I could have
>SQL = "select * from MyDB where userid = " & chr(39) & textboxuserID &
>chr(39)
>Select * from MyDB where userid = 'Tony'
>
>now if i type into the textbox
>
>Tony and userid like a%
>
>Wouldn't translate to
>
>Select * from MyDB where userid = 'Tony and userid like a%'
>
>It just looks like a bad userid to me.
>
>No matter what I type into the textbox it will be surrounded by single
>quotes.
>
>Numeric values are different and I can see how it might work.
>
>
>Tony
>
>
>
>
>
>"Spam Catcher" <spamhoneypot@r ogers.comwrote in message
>news:Xns99779F 9A01C6Fusenetho neypotrogers@12 7.0.0.1...

>Jim <joSmith2im@Rem oveThisStuffNet scape.netwrote in news:is-
>dneOzk5RFzjvbnZ 2dnUVZ_sDinZ2d@ ma...ncablet v.com:
>>

>>That did it. thanks. I'm a newbe at this and I just sniped the code
>>out of something I found that worked.

>>
>>
>FYI, the reason why I suggested not to use concatenation is because it
>exposes your code to SQL injection. As you noticed, escape characters such
>as single quotes (') can cause your SQL code to bomb out. A hacker could
>mangle the sql string with escape characters and run unexpected SQL (i.e.
>DELETE FROM TABLE) ;-)

>

[Spam Catcher]

Re: query delimiters

Jack Jackson <jacknospam@peb bleridge.comwro te in
news:a7qda3te5c k10pn9k7jlp133c 05o9hutbi@4ax.c om:

Type into the textbox:
>
a' delete from MyDB

Exactly ...


To the OP, just use SQL Parameters and you'll never have these problems.
It's recommended by Microsoft anyways.

[Tony M]

Re: query delimiters

I don't doubt you I'm just trying to learn and understand but.


here's result from your suggestion. (firstname and first value)

Dim SQLAddCus As String

SQLAddCus = "Insert INTO Membership (FirstName, LastName, LastChangeTime, MemberTimeStamp , HowFound, EMailAddress, Zip, ReadTerms, Contribute) "

SQLAddCus &= "Values(" & QM & txtFirstName.Te xt & QM & "," & QM & txtLastName.Tex t & QM & "," & QM & Now & QM & "," & QM & Now & QM & "," & QM & ddlHowYouFoundU s.SelectedValue & QM & "," & QM & txtEMail.Text & QM

SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Che cked & "," & cbContributeToN ewsLetter.Check ed & ")"


Insert INTO mydb (FirstName, LastName, LastChangeTime, MemberTimeStamp , HowFound, EMailAddress, Zip, ReadTerms, Contribute) Values('a' delelte from mydb','smith',' 7/25/2007 3:46:17 PM','7/25/2007 3:46:17 PM','Google','T ony@m222.com',' 10567',True,Tru e)
Which produces an error.

======
here's result from a select which also creates an error

Dim SQL = "Select * from MyDB where FirstName = " & Chr(39) & txtFirstName.Te xt & Chr(39)

Select * from MyDB where FirstName = 'a' delete from mydb'


thanks



"Spam Catcher" <spamhoneypot@r ogers.comwrote in message news:Xns9978196 84B808usenethon eypotrogers@127 .0.0.1...

Jack Jackson <jacknospam@peb bleridge.comwro te in
news:a7qda3te5c k10pn9k7jlp133c 05o9hutbi@4ax.c om:

>Type into the textbox:
>
>a' delete from MyDB

Exactly ...


To the OP, just use SQL Parameters and you'll never have these problems.
It's recommended by Microsoft anyways.
>

[Spam Catcher]

Re: query delimiters

"Tony M" <TonyMast_NOSPA M@msn.comwrote in
news:OLIQautzHH A.1100@TK2MSFTN GP06.phx.gbl:

I don't doubt you I'm just trying to learn and understand but.
>
>
here's result from your suggestion. (firstname and first value)
>
Dim SQLAddCus As String
>
SQLAddCus = "Insert INTO Membership (FirstName, LastName,
LastChangeTime, MemberTimeStamp , HowFound, EMailAddress, Zip,
ReadTerms, Contribute) "
>
SQLAddCus &= "Values(" & QM & txtFirstName.Te xt & QM & "," & QM &
txtLastName.Tex t & QM & "," & QM & Now & QM & "," & QM & Now & QM &
"," & QM & ddlHowYouFoundU s.SelectedValue & QM & "," & QM &
txtEMail.Text & QM
>
SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Che cked &
"," & cbContributeToN ewsLetter.Check ed & ")"
>
>
Insert INTO mydb (FirstName, LastName, LastChangeTime,
MemberTimeStamp , HowFound, EMailAddress, Zip, ReadTerms, Contribute)
Values('a' delelte from mydb','smith',' 7/25/2007 3:46:17
PM','7/25/2007 3:46:17 PM','Google','T ony@m222.com',' 10567',True,Tru e)
Which produces an error.
>
=====here's result from a select which also creates an error
>
Dim SQL = "Select * from MyDB where FirstName = " & Chr(39) &
txtFirstName.Te xt & Chr(39)
>
Select * from MyDB where FirstName = 'a' delete from mydb'

Try:

If FirstName is: a' delete from mydb;''

http://en.wikipedia.org/wiki/SQL_injection

http://www.securiteam.com/securityre...DP0N1P76E.html

[Tony M]

Re: query delimiters

Thanks
I tried some examples in the articles.
I see how this can happen now.
I need to learn how to create databases in SQL and not use MS Access anymore
then use the parameters as you suggest.
this input returns all records
a' 't' = 't

Any beginner places to learn how to create database and add fields (columns)
?

Thanks again





"Spam Catcher" <spamhoneypot@r ogers.comwrote in message
news:Xns997886C 4058C6usenethon eypotrogers@127 .0.0.1...

"Tony M" <TonyMast_NOSPA M@msn.comwrote in
news:OLIQautzHH A.1100@TK2MSFTN GP06.phx.gbl:
>

>I don't doubt you I'm just trying to learn and understand but.
>>
>>
>here's result from your suggestion. (firstname and first value)
>>
>Dim SQLAddCus As String
>>
>SQLAddCus = "Insert INTO Membership (FirstName, LastName,
>LastChangeTime , MemberTimeStamp , HowFound, EMailAddress, Zip,
>ReadTerms, Contribute) "
>>
>SQLAddCus &= "Values(" & QM & txtFirstName.Te xt & QM & "," & QM &
>txtLastName.Te xt & QM & "," & QM & Now & QM & "," & QM & Now & QM &
>"," & QM & ddlHowYouFoundU s.SelectedValue & QM & "," & QM &
>txtEMail.Tex t & QM
>>
>SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Che cked &
>"," & cbContributeToN ewsLetter.Check ed & ")"
>>
>>
>Insert INTO mydb (FirstName, LastName, LastChangeTime,
>MemberTimeStam p, HowFound, EMailAddress, Zip, ReadTerms, Contribute)
>Values('a' delelte from mydb','smith',' 7/25/2007 3:46:17
>PM','7/25/2007 3:46:17 PM','Google','T ony@m222.com',' 10567',True,Tru e)
>Which produces an error.
>>
>=====here's result from a select which also creates an error
>>
>Dim SQL = "Select * from MyDB where FirstName = " & Chr(39) &
>txtFirstName.T ext & Chr(39)
>>
>Select * from MyDB where FirstName = 'a' delete from mydb'

>
>
Try:
>
If FirstName is: a' delete from mydb;''
>

http://en.wikipedia.org/wiki/SQL_injection

>
http://www.securiteam.com/securityre...DP0N1P76E.html

[Tony M]

Re: query delimiters

I found this in case anyone else was interested.

http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

Parameters with MS Access database.

"Tony M" <TonyMast_NOSPA M@msn.comwrote in message
news:OO4e68uzHH A.2484@TK2MSFTN GP06.phx.gbl...

Thanks
I tried some examples in the articles.
I see how this can happen now.
I need to learn how to create databases in SQL and not use MS Access
anymore then use the parameters as you suggest.
this input returns all records
a' 't' = 't
>
Any beginner places to learn how to create database and add fields
(columns) ?
>
Thanks again
>
>
>
>
>
"Spam Catcher" <spamhoneypot@r ogers.comwrote in message
news:Xns997886C 4058C6usenethon eypotrogers@127 .0.0.1...

>"Tony M" <TonyMast_NOSPA M@msn.comwrote in
>news:OLIQautzH HA.1100@TK2MSFT NGP06.phx.gbl:
>>

>>I don't doubt you I'm just trying to learn and understand but.
>>>
>>>
>>here's result from your suggestion. (firstname and first value)
>>>
>>Dim SQLAddCus As String
>>>
>>SQLAddCus = "Insert INTO Membership (FirstName, LastName,
>>LastChangeTim e, MemberTimeStamp , HowFound, EMailAddress, Zip,
>>ReadTerms, Contribute) "
>>>
>>SQLAddCus &= "Values(" & QM & txtFirstName.Te xt & QM & "," & QM &
>>txtLastName.T ext & QM & "," & QM & Now & QM & "," & QM & Now & QM &
>>"," & QM & ddlHowYouFoundU s.SelectedValue & QM & "," & QM &
>>txtEMail.Te xt & QM
>>>
>>SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Che cked &
>>"," & cbContributeToN ewsLetter.Check ed & ")"
>>>
>>>
>>Insert INTO mydb (FirstName, LastName, LastChangeTime,
>>MemberTimeSta mp, HowFound, EMailAddress, Zip, ReadTerms, Contribute)
>>Values('a' delelte from mydb','smith',' 7/25/2007 3:46:17
>>PM','7/25/2007 3:46:17 PM','Google','T ony@m222.com',' 10567',True,Tru e)
>>Which produces an error.
>>>
>>=====here's result from a select which also creates an error
>>>
>>Dim SQL = "Select * from MyDB where FirstName = " & Chr(39) &
>>txtFirstName. Text & Chr(39)
>>>
>>Select * from MyDB where FirstName = 'a' delete from mydb'

>>
>>
>Try:
>>
>If FirstName is: a' delete from mydb;''
>>
>http://en.wikipedia.org/wiki/SQL_injection
>>
>http://www.securiteam.com/securityre...DP0N1P76E.html

>
>
>