Reducing Round Trips - Working with HTML Checkboxes
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/
-
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
-
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.
-
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.
-
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!
-
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
) . -
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!
-
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.
-
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 -
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
-
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.
-
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.
-
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
-
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.
-
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply