Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reducing Round Trips - Working with HTML Checkboxes Expand / Collapse
Author
Message
Posted Wednesday, June 9, 2004 1:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,908, Visits: 1,834
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/

LinkedIn Profile
Newbie on www.simple-talk.com
Post #120185
Posted Thursday, June 10, 2004 11:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2007 3:44 AM
Points: 18, Visits: 1

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

Post #120514
Posted Friday, June 11, 2004 1:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,908, Visits: 1,834

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #120522
Posted Friday, June 11, 2004 2:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 7:12 AM
Points: 115, Visits: 34

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.




Post #120527
Posted Friday, June 11, 2004 2:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,908, Visits: 1,834

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!



LinkedIn Profile
Newbie on www.simple-talk.com
Post #120529
Posted Friday, June 11, 2004 3:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 7:12 AM
Points: 115, Visits: 34

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 ) .




Post #120531
Posted Friday, June 11, 2004 3:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,331, Visits: 566

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!




Post #120536
Posted Friday, June 11, 2004 3:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,908, Visits: 1,834

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #120539
Posted Friday, June 11, 2004 1:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:11 AM
Points: 431, Visits: 604

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
Post #120703
Posted Friday, June 11, 2004 1:41 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102

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
Post #120708
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse