 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / February 2008Dynamic SQL
Hi I need to send dynamic SQL commands to the database. For example: update AffiliateAccount ( username, company, contactName, address, city, zip, state, country, homepage, email, phone, fax, pwd ) values ( "jdoe", "Johwn Doe Music Ltd", "John Doe", "Some streeed", "Los Angeles", "ZIP 345", "California", "USA", "www.johndoeltd.com", "email@johndoeltd.com", "+1 555 5555 5555", "+1 555 5555 5556", "yankedoodleday" ); The values is given by TextBoxes in an aspx page. I don't want to use GridViews for adding and manigin user information. It looks more professional if it is made by ordanary forms instead. What type ASP.NET of componant do I use for this? Can I use a DataSet or is there any SQLCommand component I can use. Any suggestin is aprecheated. Lars > I need to send dynamic SQL commands to the database. > The values is given by TextBoxes in an aspx page. Any suggestin is > appreciated. Before you go any further with this, open up Google and search for "SQL Injection"... If your site is currently live on the Internet, take it down immediately...  SignatureMark Rae ASP.NET MVP http://www.markrae.net My guess would be that this guy far far far away from real website..... :) George >> I need to send dynamic SQL commands to the database. >> The values is given by TextBoxes in an aspx page. Any suggestin is [quoted text clipped - 5 lines] > If your site is currently live on the Internet, take it down > immediately... HI The problem was easy to solve. I used s SQLDataSource and set the SelectCommand to access the information. Once I found out the way to do it it was as easy as a pie. Here's the code protected void pbEdit_Click(object sender, EventArgs e) { sdsAffiliateAccount.SelectCommand = "select username, company, contactName, address, city, zip, state, country, homepage, " + " email, phone, fax, pwd from AffiliateAccount " + "where ('" + tbUsername.Text + "' = username) and ('" + tbPassword.Text + "' = pwd);"; } Sice I have a DetailesView connected to the SQLDataSOurce the data fills the form nicely. Lars I do have a website that works this way but it's written in PHP and HTML. Works just fine. My affiliates can log in and track how many installs they have distributed. I can also track the number of installs my products have. Note NON unique installs. To track unique installs within EU is illegal. The purpose for the database is to make sure my affiliate gets paid for every install they generate. My website have more than 1000 visitors a day. Far from a real web site? Far from a real ASP.NET site? Not that, I just need to set up the database and hoast it some place. Don't know if my current ISP supports ASP.NET. But yes I'm new to ASP.NET. Lars > My guess would be that this guy far far far away from real website..... > :) [quoted text clipped - 9 lines] >> If your site is currently live on the Internet, take it down >> immediately... > Once I found out the way to do it it was as easy as a pie. > [quoted text clipped - 9 lines] > + "' = pwd);"; > } Oh my God!!! Please tell me that this isn't on the live Internet... SignatureMark Rae ASP.NET MVP http://www.markrae.net Hi Since I'm nerw to ASP.NET can you okease tell me why this is dangerous? No, this actual source is NOT live on the Internet. Any other suggestion that doesn't need to add users to the database. Lars >> Once I found out the way to do it it was as easy as a pie. >> [quoted text clipped - 11 lines] > > Oh my God!!! Please tell me that this isn't on the live Internet... You should Google for "SQL Injection" as Mark suggested in his first reply. Have you tried logging in as this user? N00bHax0r') or 1=1; -- > Hi > [quoted text clipped - 20 lines] >> >> Oh my God!!! Please tell me that this isn't on the live Internet... Login where? To the server or Windows. The security issue is not that improtant on the webpages. The page shall not be live 24/7 only when I do the demos. Don't need to login to the database. There's no users created to it yet. THe user creates their own account. Anyhow I still don't see the problem, of course the real live site needs checking that the right carracters are entered to the fields. OF course entering username N00bHax0r') will cause an error. Isn't that vat the validators are for? Can I use any validator to test this. Some how the user must enter username and password and the other data the vry first time. I get your point and the problem is familiure to me. I deal with that type of input checking in all my exe program. But thanks for reminding me. At the moment my focus is to get the database work, understanding how ASP.NET deals with database. The finnish of input forms will be dealth with at a later time. protected void pbEdit_Click(object sender, EventArgs e) { // Of course: // Before calling this make sure every field has valid caracters. // is the Validators good for this ????? sdsAffiliateAccount.SelectCommand = "select username, company, contactName, address, city, zip, state, country, homepage, " + " email, phone, fax, pwd from AffiliateAccount " + "where ('" + tbUsername.Text + "' = username) and ('" + tbPassword.Text + "' = pwd);"; } > You should Google for "SQL Injection" as Mark suggested in his first > reply. [quoted text clipped - 27 lines] >>> >>> Oh my God!!! Please tell me that this isn't on the live Internet... > Since I'm new to ASP.NET can you please tell me why this is dangerous? I already told you - SQL Injection: http://technet.microsoft.com/en-us/library/ms161953.aspx > No, this actual source is NOT live on the Internet. Glad to hear it. > Any other suggestion that doesn't need to add users to the database. Do not, under *ANY* circumstances, build up dynamic SQL from form fields... E.g., if someone were to enter the data below in your tbUsername TextBox: 1=0); DROP TABLE AffiliateAccount;-- the resulting SQL sent to the database would be something like: select username, company, contactName, address, city, zip, state, country, homepage, email, phone, fax, pwd from AffiliateAccount where (1=0); DROP TABLE AffiliateAccount; --= username) and ('MyPassword' = pwd); The first line is perfectly valid SQL, and it would be processed, returning no data. The second line (the DROP TABLE line) is also perfectly valid SQL and would also be processed. The third line would be treated as a comment and, therefore, ignored. SignatureMark Rae ASP.NET MVP http://www.markrae.net > E.g., if someone were to enter the data below in your tbUsername TextBox: > > 1=0); DROP TABLE AffiliateAccount;-- Get the point. I guess I have to validate the commands before sending them to the SQL Server. When I write programs that access databases with dynamic SQL you always make sure that the data entered is correct. But you get the problem any how I guess the very first time the use has to enter data. If you know please advice how to use the validarors for this. Lars > the resulting SQL sent to the database would be something like: > [quoted text clipped - 10 lines] > would also be processed. > The third line would be treated as a comment and, therefore, ignored. > Get the point. I guess I have to validate the commands before sending them > to the SQL Server. When I write programs that access databases with > dynamic SQL you always make sure that the data entered is correct. No you don't! You simply *NEVER EVER* use dynamic SQL built up from form fields... You use either parameterised queries or stored procedures... SignatureMark Rae ASP.NET MVP http://www.markrae.net Hi Stored procedures is a good suggestion i guess. Although a bit more complicated. As for parameterised values doesn't that give the same type of errors. Suggestion you have a stored procedure. How would you avoid the problem. Doesn't stored procedures have to deal with SQL commands? Can a stored procedure use parameterised quiries. How does such look. Doesn't parameterised updates or selections generate SQL commands? If you have worked with ASP.NET how do I add parameters to SQL commands. I have done similar in Delphi and their Database components. Although it was 8 years ago. Thanks for your advise. Lars >> Get the point. I guess I have to validate the commands before sending >> them to the SQL Server. When I write programs that access databases with >> dynamic SQL you always make sure that the data entered is correct. > > No you don't! You simply *NEVER EVER* use dynamic SQL built up from form > fields... As long as you make sure there is impossible to enter fault data and check that *NEVER EVER* SQL commands parameters (values) are faulty or have incorrect characters I see no problem. Thenyou can not enter 1=0); DROP TABLE AffiliateAccount; --= There are incorrect characters in that line. But then this must be checked just before sending the SQL command. > You use either parameterised queries or stored procedures... > Thanks for your advice. Don't take this the wrong way, but you really should consider a basic introduction to ASP.NET rather than hacking around with it... You're going to find it a huge and frustrating uphill struggle unless you gain a fundamental understanding of how it works... I suggest you get a copy of this: http://www.amazon.com/ASP-NET-3-5-Dummies-Computer-Tech/dp/0470195924/ref=pd_bbs _sr_3?ie=UTF8&s=books&qid=1202416148&sr=8-3 and work your way through it... SignatureMark Rae ASP.NET MVP http://www.markrae.net >> Thanks for your advice. > > Don't take this the wrong way, but you really should consider a basic > introduction to ASP.NET rather than hacking around with it... You're going > to find it a huge and frustrating uphill struggle unless you gain a > fundamental understanding of how it works... I don't take it the wrong way, not as a professional programmer. I have some introduction Videos that I'm look at. But it takes a lot of time. It would be beter to have a good book. So far I 've comed to the section of GridViews and DetailesView, SQLDataSource adding and managing. Adding a SQL Database to the projct. The videos also introduced the Master Page technique that I find very useful for my projects. I downloaded the videos for free at www.LearnVisualStudio.net. Paying for a crash course is out of the option at the moment. And there aren't that many available where I live. Have to go away for a week or two for tousands of dollars. Any suggestion for self studies are aprecheated. Lars > I suggest you get a copy of this: > http://www.amazon.com/ASP-NET-3-5-Dummies-Computer-Tech/dp/0470195924/ref=pd_bbs _sr_3?ie=UTF8&s=books&qid=1202416148&sr=8-3 > and work your way through it... > Any suggestion for self studies are aprecheated. Buy the book I suggested, and work your way through it... It's not very expensive, and you should be able to get through it in a weekend... SignatureMark Rae ASP.NET MVP http://www.markrae.net >> Get the point. I guess I have to validate the commands before sending >> them to the SQL Server. When I write programs that access databases with [quoted text clipped - 4 lines] > > You use either parameterised queries or stored procedures... I got the point and changed to parameterised update. However when using the followng SQL command. select * from [aTable] where [username] = @username; What happens when the username has the value SOMEUSER and that user exists..... "SOMEUSER";DROP TABLE [aTable];--= *) Does the SQLDataSource only send one SQL command to the server? or is this server dependant. Shurley you would get the same program with Stored procedures. *) Does the SQLDataSource component generate SQL commands that are sent to the database? *) If so we still have the problemas if I would build Strings and send to the server. *) Do I have to add protection so the user can't enter invalid characters such as ";" and how do you do that in C# and ASP.NET? As far as I see there's no other way to be sure than to make sure the user can't enter invalid values to the database. Does C# have any build in functions to check this. I use the validators to verify password and for making sure the user enters no empty (null) fields. But feel I need to a validators so that the use can't enter an invalid parameter. For example a phone number should only insude numbers and spaces. A string field should be alpha numeric etc. The demo viedeos haven't discussed this (yet). But the videos are to slow. Have to watch a video for 30 minutes just for the guy to show how to add a FormsView. Readding the database connection string, same select commands again and again. This is trivial to me. Upon that the guy in the videos uses "absolute positioning", brrr. Lars >>> Get the point. I guess I have to validate the commands before sending >>> them to the SQL Server. When I write programs that access databases with [quoted text clipped - 14 lines] > > "SOMEUSER";DROP TABLE [aTable];--= http://msdn2.microsoft.com/en-us/library/ms998271.aspx SignatureMark Rae ASP.NET MVP http://www.markrae.net >>>> Get the point. I guess I have to validate the commands before sending >>>> them to the SQL Server. When I write programs that access databases [quoted text clipped - 16 lines] > > http://msdn2.microsoft.com/en-us/library/ms998271.aspx Mark, it's funny how this explains another problem I had. In another database I have URLs stored in the database. To make things easy I thought I'd enter the html code it self and view that in a ViewList. But what I got out was the chtml code written with html excape sequences. The document you now showed me explains why. When I enter the < and " into the Data Editor it replaces it with escape characters like in a C++ string "\\" or "\n". If I get it correct using .NET Parameterised inputs the parser replaces for example ; with an excape sequence leaving my tables safe. Of course if you have a large database used by many people there should be one user as administrator or root if you like. The ordinary user such as affiliates should only be allowed update, insert and delte user data. Not delete the table it self. Since this project is mainly to lear ASP.NET and not databases this managing in the database sin't my main goal. Quote from the page ========== Start by constraining input in the server-side code for your ASP.NET Web pages. Do not rely on client-side validation because it can be easily bypassed. Use client-side validation only to reduce round trips and to improve the user experience. ====== I have said this many times. If the user can do wrong some one sooner or later will do so. When Binding a RegularExpressionValidator what does the following mean. Eval("company", "{0}") I only saw examples how to format date, time, numbers etc and no way of format strings. Does this mean that any string you enter is legal. How do you evaluate for alpha numerical characters spaces and underscores. Is there a way to let the validator test for a cet of characters. For example "#0123456789"? When I tested to write ";;;;;;;;;;;;;;;;" in a TextBox it stored ";;;;;;;;;;;;;;;;" in the database. Lars Great page, pretty much what I needed. Tried this <tr> <td align="right" class="style6">Username</td> <td class="style7"> <asp:TextBox ID="tbInsertUsername" runat="server" class="style_datafield" Text='<%# Bind("username") %>' /> <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="tbInsertCompany" ErrorMessage="Comapny can only be characters and blanks" ValidationExpression=""a-zA-Z 0-9{6,50}"">*</asp:RegularExpressionValidator> </td> <td> <asp:RequiredFieldValidator ID="rfvInsertUsername" runat="server" ErrorMessage="Username is iequired" ControlToValidate="tbInsertUsername">*</asp:RequiredFieldValidator> </td> </tr> Questions: Can I use both RequiredFieldValidator and RegularExpressionValidator for the same Control. If RequiredFieldValidator works correctly users shouldn't be allowed to enter empty tbInsertUsername. It doesn't seam to do what I want. I can still enter )#/¤#= BTW In stead of using InsertCostomer<TextBox> I us <tb>InsertCustomer naming style. An old habit from a crash course I took in SQL Windows 11 years ago. Still use the same for all my projects. lbl for Labels. Lars >>>>> Get the point. I guess I have to validate the commands before sending >>>>> them to the SQL Server. When I write programs that access databases [quoted text clipped - 61 lines] > > Lars >>> Get the point. I guess I have to validate the commands before sending >>> them to the SQL Server. When I write programs that access databases with [quoted text clipped - 38 lines] > select commands again and again. This is trivial to me. Upon that the guy > in the videos uses "absolute positioning", brrr. http://msdn2.microsoft.com/en-us/library/ms161953.aspx Use SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth. To constraint the user to specific text/format use the validatoin controls in ASP.NET e.g. RegularExpressionValidator Also, in the rare event when the client side validation did not work (jscript / browser) you must also validate the input in the server side code -- Misbah Arefin >>> Get the point. I guess I have to validate the commands before sending >>> them to the SQL Server. When I write programs that access databases with [quoted text clipped - 40 lines] > > Lars User Input Validation in ASP.NET http://msdn2.microsoft.com/en-us/library/ms972961.aspx Signature-- Misbah Arefin > Use SQL parameters for data access. You can use these parameters with > stored procedures or dynamically constructed SQL command strings. [quoted text clipped - 58 lines] >> >> Lars > Hi > [quoted text clipped - 43 lines] > > Lars Interestingly enough, the SQLCommand component is called SqlCommand. :) SqlConnection cn = new SqlConnection("my_connection_string"); SqlCommand cmd = new SqlCommand("update AffiliateAccount set company = @company where keyvalue = @keyvalue", cn); cmd.Parameters.AddWithValue("@company", TextBox1.Text); cmd.Parameters.AddWithValue("@keyvalue", SomeKeyValue); cmd.ExecuteNonQuery(); As Mark Rae implied, simply coding GUI text directly into the SQL statement is a bad idea. Be sure to use query parameters (or stored procedures if you prefer). 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.