Dynamic SQL

Discussion in 'ASP .Net' started by Mr. R, Feb 6, 2008.

  1. Mr. R

    Mr. R Guest

    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",
    "",
    "+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
     
    Mr. R, Feb 6, 2008
    #1
    1. Advertisements

  2. 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...
     
    Mark Rae [MVP], Feb 6, 2008
    #2
    1. Advertisements

  3. 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).
     
    Scott Roberts, Feb 6, 2008
    #3
  4. My guess would be that this guy far far far away from real website.....
    :)
    George
     
    George Ter-Saakov, Feb 7, 2008
    #4
  5. Mr. R

    Mr. R Guest

    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

     
    Mr. R, Feb 7, 2008
    #5
  6. Oh my God!!! Please tell me that this isn't on the live Internet...
     
    Mark Rae [MVP], Feb 7, 2008
    #6
  7. Mr. R

    Mr. R Guest

    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
     
    Mr. R, Feb 7, 2008
    #7
  8. 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; --



     
    Scott Roberts, Feb 7, 2008
    #8
  9. I already told you - SQL Injection:
    http://technet.microsoft.com/en-us/library/ms161953.aspx
    Glad to hear it.
    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.
     
    Mark Rae [MVP], Feb 7, 2008
    #9
  10. Mr. R

    Mr. R Guest

    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);";
    }

     
    Mr. R, Feb 7, 2008
    #10
  11. Mr. R

    Mr. R Guest

    E.g., if someone were to enter the data below in your tbUsername TextBox:
    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
     
    Mr. R, Feb 7, 2008
    #11
  12. No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
    fields...

    You use either parameterised queries or stored procedures...
     
    Mark Rae [MVP], Feb 7, 2008
    #12
  13. Mr. R

    Mr. R Guest

    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


    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.
     
    Mr. R, Feb 7, 2008
    #13
  14. 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-D...bs_sr_3?ie=UTF8&s=books&qid=1202416148&sr=8-3
    and work your way through it...
     
    Mark Rae [MVP], Feb 7, 2008
    #14
  15. Mr. R

    Mr. R Guest

    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
     
    Mr. R, Feb 7, 2008
    #15
  16. 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...
     
    Mark Rae [MVP], Feb 7, 2008
    #16
  17. Mr. R

    Mr. R Guest

    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
     
    Mr. R, Feb 8, 2008
    #17
  18. http://msdn2.microsoft.com/en-us/library/ms998271.aspx
     
    Mark Rae [MVP], Feb 8, 2008
    #18
  19. http://msdn2.microsoft.com/en-us/library/ms161953.aspx
     
    Scott Roberts, Feb 8, 2008
    #19
  20. 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



     
    Misbah Arefin, Feb 8, 2008
    #20
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.