 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / VB.NET / July 2007query delimiters
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) & "');" > 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. >> 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 <joSmith2im@RemoveThisStuffNetscape.net> wrote in news:is- dneOzk5RFzjvbnZ2dnUVZ_sDinZ2d@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 > Jim <joSmith2im@RemoveThisStuffNetscape.net> wrote in news:is- > dneOzk5RFzjvbnZ2dnUVZ_sDinZ2d@massilloncabletv.com: [quoted text clipped - 7 lines] > mangle the sql string with escape characters and run unexpected SQL (i.e. > DELETE FROM TABLE) ;-) Type into the textbox: a' delete from MyDB >I don't understand and I know I'm wrong but... > [quoted text clipped - 31 lines] >> mangle the sql string with escape characters and run unexpected SQL (i.e. >> DELETE FROM TABLE) ;-) > 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','Tony@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 >> Type into the textbox: >> [quoted text clipped - 4 lines] > 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. > [quoted text clipped - 26 lines] > > 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 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 >> I don't doubt you I'm just trying to learn and understand but. >> [quoted text clipped - 34 lines] > > http://www.securiteam.com/securityreviews/5DP0N1P76E.html I found this in case anyone else was interested. http://www.mikesdotnetting.com/Article.aspx?ArticleID=26 Parameters with MS Access database. > Thanks > I tried some examples in the articles. [quoted text clipped - 47 lines] >> >> http://www.securiteam.com/securityreviews/5DP0N1P76E.html Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.