Reducing Round Trips - Working with HTML Checkboxes

  • David.Poole

    SSC Guru

    Points: 75198

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/

  • Anton Damhuis

    Old Hand

    Points: 310

    The example will work well for Intranet sites, but would become a security risk for Internet Sites.

    Reason:

    From the article; "....Note that the value attribute contains the primary key value of the topic and that the id attribute..." This there were a filter on the values displayed, for example no ID9 was displayed and attacker could alter one of the values to 9 and then submit the checked value back to the server, and the server would run through its code and add the value 9 to the Database.

    So there needs to be extra security measures in place when using this sample on internet enabled web sites. The concept is good the Internet implementation needs some extra work.

    Regards

       Anton

  • David.Poole

    SSC Guru

    Points: 75198

    I don't agree because the topic id is not private information.  In fact in the live application the topics are SIC codes so the SIC code is actually displayed on the web page!

    If the page worked the other way around and we had a single topic and had to check boxes to indicate contacts to whom it applied then yes, it wouldn't be a good idea.

    Where possible we use NT security and any user can only see their own records.  Any pages that allow admin functionality that would allow other users to be viewed is not published to the live server, but stay safely on the staging server, which cannot be accessed externally.

  • Mauro Ciaccio

    SSCrazy

    Points: 2557

    Hi,

    Wow. Now that is serious coding!

    But I'm not quite sure if it's not overkill. I have an identical requirement where I need to post back and save multiple checkbox selections from a web server.

    The way I have gotten around it is the following:

    1) Implement client-side JavaScript to build a comma-separated string of IDs to return

    2) Build the comma-separated string on the onclick event of the SUBMIT button and post to the action asp page.

    3) Retrieve the string and call a DLL (a stored procedure would also work)

    4) Parse the string and for each ID save to the database

    You mention that your approach is significantly quicker respect to iterating on the web server and saving each check-box value and I absolutely agree. But I would not expect the approach above to be significantly slower than the approach you propose.

    I'd be interested in hearing your views.

  • David.Poole

    SSC Guru

    Points: 75198

    We develop a lot of UK government web-sites and accessibility is a major issue.  That is, could this site be used by someone who is blind, can't use a mouse or keyboard, has some form of special browser that does not support Javascript.

    Remember also that someone can switch Javascript off in normal browsers.

    For this reason we either don't use client side Javascript, or provide <NOSCRIPT> alternatives.  We tend to favour the former because developing client side Javascript means doubling of effort as we have to provide the <NOSCRIPT> alternative in any case.

    On the performance front we find that sites that work fine with a few thousand users fall to bits when a few tens or thousands of users hit them.

    I've said it many times on SQLServerCentral, this is A solution not THE solution.  If your solution works and can cope with the site traffic you have and the site audience then you have produced successful code.

    There is another aspect to this which I merely hinted at in my article.  By shifting the complicated stuff onto the database server I am making the programmers task a lot easier and for that they are extremely grateful. 

    Their appreciation manifests itself in lots of ways, in order of priority

    • Free beer.
    • They get me involved at the design stage.
    • They ask for their SQL to be vetted.
    • They preach stored procedures with the zeal of the converted.

    OK, it increases my workload, but that is what I get paid for!

  • Mauro Ciaccio

    SSCrazy

    Points: 2557

    Amazing what one ends up taking for granted. Because we develop B2B web sites where we can specify the requirements for the client browsers, the option of not running JavaScript is not even dreamt about. But I can see how for your needs client side JavaScript is not a solution.

    I will certainly keep a note of your solution, but for the time being I think we shall stick with ours.

    But I agree that shifting logic to the database is good whenever it can be done.

    As for the appreciation, I have been preaching stored procedures to the point that our ASP guy thinks I'm just doing it to get him out of the database (....which I am ) .

  • richardd

    Hall of Fame

    Points: 3899

    Speaking of security risks, how about this line:

    _topic = new String(request.chktopic);

    ...

    var sql="!nocache:execsp usp_setcontacttopic "

    ... + ", '" + _topic + "'";

    That's a great way to leave your site open to SQL Injection attacks!

  • David.Poole

    SSC Guru

    Points: 75198

    Strictly speaking yes, it would be at risk from an injection attack, but not in the case of this particular CMS.

    The execsp portion tells the CMS that this is a stored procedure and it doesn't understand multiple commands, so if you try and fudge the chkTopics array to contain ";SELECT * from <table>" it will simply throw an error.  Ditto, executing a second stored procedure.

    The error trapping code in the SSJS is quite extensive and successfully traps injection attempts.  I know, it has been extensively tested.

    The user under which the CMS runs has specific execute rights to specific stored procedures and the live server has the majority of the system stored procedures locked down as per Brian Knight's security document.

    I wish the CMS allowed request.form.field, but it doesn't.

    Strictly speaking I should have written a testing function such as

    function fnNumericArray(aValue){

       var bReturn=true;

       var sJoin=new String(aValue.toString);

       var aTest = sJoin.split(",");

       for(var x=0;x<aTest.length;x++){

            if(isNaN(aTest[x]){

              bReturn=false;

              break;

            }

        }

        return bReturn;

    }

    Again, strictly speaking I should have a try, throw, catch constructs to test that my value IS an array.

  • Michael Lato

    Ten Centuries

    Points: 1278

    I had a thought I wanted to put out for general response.  While I certainly encapsulate my code into stored procedures whenever possible, I also keep as much "business logic" out of the database as possible in order to speed operations when scaling.

    My thoughts are as follows: the database systems must be carefully configured to either scale up or out as your online usership grows.  However, web servers are very simple to scale outward by clustering and "throwing another box at it".

    Any thoughts along these lines?

    Regards,
    Michael Lato

  • Calvin Lawson

    SSChampion

    Points: 11030

    Pretty good article.  A great solution you came up with; probably saved a lot of database overhead by using an array.  I know this is against standard practices, but I guess only when you know the rules can you break them to good effect.  The cost of passing a string rather than 100 indvidual ints (or bits) is much lower.

    I might add that your proc will only work on numeric data; you should indicate that in the name or comments:

    Also, here's an alternative solution

     

    create function UTIL_parseIntArray(@Array varchar(8000), @delimiter varchar(8000))

    returns @intID table (ID int Primary Key)

    as

    begin

     

    declare @ID varchar(30),

      @charindex int,

      @len int

    select  @Array   = rtrim(ltrim(@Array)),

      @len   = len(@Array),

      @charindex  =  charindex(@delimiter, @array)

    While @charindex > 0

     BEGIN

      

      select 

      @ID   =  left(@Array, @charindex-1),

      @len  =  @len - @charindex,

      @Array  =  right(@Array, @len),

      @charindex  =  charindex(@delimiter, @array)

      Insert @intID values (@ID)

     END

    --get last value

    Insert @intID VAlues (@Array)

    return

    end

     

    Signature is NULL

  • David.Poole

    SSC Guru

    Points: 75198

    I take your point on upscaling webservers.  I wish I knew more on the infrastructure side.  Come to think of it, I wish I knew ANYTHING on the infrastructure side

    Having talked to the C++ programmers I get the impression that string manipulation is regarded as a necessary evil.  The criticism they make of my approach is that SQL shouldn't be used for string manipulation.

    One point on the use of client side Javascript.  There are moves afoot in the UK to make it an offence to create a web-site providing a service to the public that discriminates against people with disabilities.

    This is being interpretted as meaning that if your site cannot be used by a blind person, or someone who cannot use a mouse or keyboard then you are discriminating against that person.  There is talk of extending this to the commercial sector which would affect B2B and B2C.

    I'm not going to argue the practicalities or morality of this, but UK politicians seem to implement all sorts of things without thinking them through.  Sheer stupidity is not seen as an impediment to an idea.

  • radu2k

    Grasshopper

    Points: 18

    Hi,

    I think that the same thing can be obtained in a much simpler and effective manner.

    The problem consists in updating the IsSelected flag for all topics of a specific contact, according to a configuration of checkboxes. So the web page will build a string containing the id's of checked topics, separated by a non-numeric separator (let's use '#' as separator). The string must begin and end with the specified separator. The update stored procedure might look like that.

    CREATE PROCEDURE UpdateContactTopics

    @ContactID int,

    @ContactTopics varchar(1000) = ''        -- size adjusted as needed

    AS

    UPDATE Tbl_ContactTopic

    SET

    IsSelected = CASE WHEN CHARINDEX('|' + CONVERT(varchar, TopicID) + '|', @ContactTopics) > 0 THEN 1 ELSE 0 END

    WHERE

    ContactID = @ContactID

    END

     

    The example works identical on single/multiple checked topics and can be easily extended for updating all  topics or all topics of a specific type. I have not included parameters checking or error handling as I wanted only to illustrate the concept.

     

  • Daniel Stefaniu

    SSC Enthusiast

    Points: 123

    When U have multiple items (an array) U can use an xml (create an xml on web server) and send it to the SQL server.

    The SQL has the capability of using an xml as a table.

    It should be easy enough.

     


    Daniel

  • mgaert

    SSC Journeyman

    Points: 91

    Quick note for a solution to the missing checkboxes problem. Create a hidden tag for each checkbox needed, and just use the checkboxes to change the values in the hidden tags. Something like this:

    <input type="checkbox" tabindex="1"

     name="MoreInfo_aid"

     id="MoreInfo_aid"   onclick="document.all.MoreInfo.value=document.all.MoreInfo_aid.checked;" />

    <!-- Matching hidden tag -->

    <input type="hidden" id="MoreInfo" name="MoreInfo" value="false" />

    We don't actually write all this HTML for each checkbox; a framework handles that. The best thing about this is it follows the usual POST-ing format for FORMs. Sometimes you have to add server code to ignore form elements with "_aid". You also need client side javascript.

    All solutions have their provisos.

     

     

  • Mark Leaver

    SSC Journeyman

    Points: 98

    In the printable version, for some reason the SSJS doesnt come through and it is replaced with another copy of fnSplitter.

    Apart from that, it is a good article and I am going to have a play with it to see if I can do anything with an array of checkboxes on one of our pages.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply