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) & "');"

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Jack Jackson]

Type into the textbox:

a' delete from MyDB

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

 

[Guest]

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]

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','(e-mail address removed)','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

 

[Guest]

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','(e-mail address removed)','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/securityreviews/5DP0N1P76E.html

 

[Tony M]

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

 

[Tony M]

I found this in case anyone else was interested.

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