473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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) & "');"
Jul 24 '07 #1
10 1117
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
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
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
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
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
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
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: uc_sk | last post by:
Hello All I am a newbie to PERL language...If i have a file with data of form abcd 4 {1,2,3} 3 lmn- 3 {12,18,19,22} 4 then i can read them as... ($list $listTotal $set $noElements) = split /...
6
by: jason | last post by:
I am picking up an error message on a straightforward INSERT - do I need an optimistic-type to get this working....here is is the error: Microsoft JET Database Engine error '80004005' Operation...
7
by: What-a-Tool | last post by:
Have no problem getting my select queries to work using this method: strSQL = "SELECT tblUI.IPAdd FROM tblUI WHERE (tblUI.IPAdd =?)" arSPrm = Array(strRemHst) Set rst = cmd.Execute(,arSPrm)...
1
by: nilsonj | last post by:
Trying to set up an admin page for an online training registration page. I am having trouble with a sql statement. Here is the info on the tables. I have two tables: classPerson and reglist ...
15
by: C# Learner | last post by:
Hi, I have a string (System.String) which holds some data. This data is encoding in UTF8 (i.e. anywhere in the string where there should be a single 'é' character, there will be two characters...
5
by: Kaspa | last post by:
I am creating a contact database and I would like the user to create his own groups of contacts based on criterias, thefore I would like to let him pick the fields and criteria for a particular...
5
by: hardik | last post by:
hi friends i need help in this sql query i have table like, id fid __ _____ autonumber text and i am storing values like
8
by: shorti | last post by:
DB2 V8.2 on AIX I am looking for an efficient way to update several columns in a table that will have a default change. The problem is the table is large (million records) and there are 1 to 4...
6
m6s
by: m6s | last post by:
1. After hours of researching, I used these snippets : void Object::TokenizeLines(const string& str, vector<string>& tokens, const string& delimiters) // Skip delimiters at beginning....
5
by: gpaps87 | last post by:
hi, i wanted to know whether we can use strtok command to mark delimiters as tokens as well.In Java,we have a command: StringTokennizer(String str, String delimiters, boolean delimAsToken) ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.