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) & "');" 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.
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.
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) ;-)
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) ;-)
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) ;-)
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.
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.
"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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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 /...
|
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...
|
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)...
|
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
...
|
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...
|
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...
|
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
|
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...
|
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....
|
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)
...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |