By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,031 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,031 IT Pros & Developers. It's quick & easy.

query delimiters

P: n/a
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) & "');"
Jul 24 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Jim <jo********@RemoveThisStuffNetscape.netwrote in
news:ON******************************@massilloncab letv.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.
Jul 24 '07 #2

P: n/a
Jim
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 <jo********@RemoveThisStuffNetscape.netwrote in
news:ON******************************@massilloncab letv.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.
Jul 24 '07 #3

P: n/a
Jim <jo********@RemoveThisStuffNetscape.netwrote in news:is-
dn***************************@massilloncabletv.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) ;-)
Jul 24 '07 #4

P: n/a
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" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
Jim <jo********@RemoveThisStuffNetscape.netwrote in news:is-
dn***************************@massilloncabletv.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) ;-)

Jul 25 '07 #5

P: n/a
Type into the textbox:

a' delete from MyDB

On Tue, 24 Jul 2007 19:55:08 -0400, "Tony M" <To*************@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" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0 .1...
>Jim <jo********@RemoveThisStuffNetscape.netwrote in news:is-
dn***************************@massilloncabletv.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) ;-)
Jul 25 '07 #6

P: n/a
Jack Jackson <ja********@pebbleridge.comwrote in
news:a7********************************@4ax.com:
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.
Jul 25 '07 #7

P: n/a
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.Text & QM & "," & QM & txtLastName.Text & QM & "," & QM & Now & QM & "," & QM & Now & QM & "," & QM & ddlHowYouFoundUs.SelectedValue & QM & "," & QM & txtEMail.Text & QM

SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Checked & "," & cbContributeToNewsLetter.Checked & ")"
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***@m222.com','10567',True,True)
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'
thanks

"Spam Catcher" <sp**********@rogers.comwrote in message news:Xn**********************************@127.0.0. 1...
Jack Jackson <ja********@pebbleridge.comwrote in
news:a7********************************@4ax.com:
>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.
Jul 25 '07 #8

P: n/a
"Tony M" <To*************@msn.comwrote in
news:OL**************@TK2MSFTNGP06.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.Text & QM & "," & QM &
txtLastName.Text & QM & "," & QM & Now & QM & "," & QM & Now & QM &
"," & QM & ddlHowYouFoundUs.SelectedValue & QM & "," & QM &
txtEMail.Text & QM

SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Checked &
"," & cbContributeToNewsLetter.Checked & ")"
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***@m222.com','10567',True,True)
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
Jul 25 '07 #9

P: n/a
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" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
"Tony M" <To*************@msn.comwrote in
news:OL**************@TK2MSFTNGP06.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.Text & QM & "," & QM &
txtLastName.Text & QM & "," & QM & Now & QM & "," & QM & Now & QM &
"," & QM & ddlHowYouFoundUs.SelectedValue & QM & "," & QM &
txtEMail.Text & QM

SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Checked &
"," & cbContributeToNewsLetter.Checked & ")"
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***@m222.com','10567',True,True)
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

Jul 25 '07 #10

P: n/a
I found this in case anyone else was interested.

http://www.mikesdotnetting.com/Artic...x?ArticleID=26
Parameters with MS Access database.

"Tony M" <To*************@msn.comwrote in message
news:OO**************@TK2MSFTNGP06.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" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
>"Tony M" <To*************@msn.comwrote in
news:OL**************@TK2MSFTNGP06.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.Text & QM & "," & QM &
txtLastName.Text & QM & "," & QM & Now & QM & "," & QM & Now & QM &
"," & QM & ddlHowYouFoundUs.SelectedValue & QM & "," & QM &
txtEMail.Text & QM

SQLAddCus &= "," & QM & txtZip.Text & QM & "," & cbReadTerms.Checked &
"," & cbContributeToNewsLetter.Checked & ")"
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***@m222.com','10567',True,True)
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



Jul 25 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.