re-ordering records in browser 1

[eljacobs]

Hi

I have designed a basic online catalogue that displays records from an access database.

I would like to give the administrator of the site the option to re-order the records into any order that they like within the admin section of the site.

What is the best way to approach this?

Thanks

Elliot

 

[theniteowl]

Do you mean permanently re-order the data or selectively per viewing?
You could have a simple form with select lists for what field to use for the OrderBy of the SQL. Pass that value to the ASP page and add it to the SQL query to control the ordering.
You could dynamically build your entire SQL query giving them options on what they want to display as well as it's order.

If you want them to be able to alter the default display of the data then they need to alter the query, or you need multiple possible queries and they alter the code to include the one they want to use.
Or to get fancy, you give them a menu to select which query to make default then use code to write out the query into a file on the server that is included in the ASP page.
You will have to make sure caching is turned off.


Stamp out, eliminate and abolish redundancy!

 

[eljacobs]

I want to allow them to permanently re-oreder the data so that they can choose which item is displayed first, second, third etc.

From a functionality point of view i guess the obvious way would be to have an up and down arrow next to each item so that if you click the up arrow the item moves up the list and the item above drops down one place.

How would i do that?

Thanks

Elliot

 

[theniteowl]

Create a form with a select box giving all the options for sorting the data. Have that form submit to an asp page where you read the selected value and write it to a text file with code something like this.

<% 
SortField = Request.Form("SortField")
Set fs = CreateObject("Scripting.FileSystemObject") 
Set wfile = fs.CreateTextFile("c:\Mydir\myfile.txt", True) 
wfile.Write (SortField) 
wfile.close 
Set wfile=nothing 
Set fs=nothing 

response.write("File created") 
%>

Note: The value True in the Set wfile line above tells it to overwrite the file if it already exists.

Now your text file exists containing the selected value from the form.

In your SQL query ASP page you read in that text file and use it's value as the parameter for your query.

<% 
Set fs = CreateObject("Scripting.FileSystemObject") 
Set wfile = fs.OpenTextFile("c:\Mydir\myfile.txt") 
SortField = wfile.ReadAll 
wfile.close 
Set wfile=nothing 
Set fs=nothing 

'Then your SQL query would be something like...
mySQL = "Select firstfield, secondfield FROM mytable WHERE something = 'something' ORDER BY " & SortField
%>

Your SQL query would always get it's order by value from the text file.  Your administrators can overwrite that text file simply by selecting a new option in the form.

You will have to make sure that the IUSR account has permissions to read/write from the folder you want the text file to exist in.  If you run into problems search on security issues using the FileSystemObject.


Stamp out, eliminate and abolish redundancy!

 

[damber]

Maybe I'm misunderstanding the request, but it sounds like the OP wants specific ordering based on a users (admin) choice - i.e. rows 1 2 3 4 5 might be ordered 4 3 5 2 1 - just because that's how they want to present them.

If that is what you want to do, then add an extra column in the product table, called 'sort_order' or similar, then allow the admin user to modify this field to be from 1 to the number of records in the table - e.g. for 5 records: 1 2 3 4 5 then use [tt]ORDER BY sort_order ASC[/tt] in your query. Then they can set the sort order however they please.

You will need to ensure that appropriate controls are put in place to validate the user selection - e.g. that they don't put 1 2 2 2 4 as the sort_order. though of course, you can allow that if you wish.

And you can of course use javascript to manage the 'up/down' arrows which will swap the current items sort_order number with the adjacent items sort_order number.

hope that helps.

A smile is worth a thousand kind words. So smile, it's easy!

 

[emozley]

Let's say your page is called searchresults.asp and you had a table with people's firstname, surname and telephone extension in.

<%

sort=Request.QueryString("sort")
If sort="" Then sort="FirstName ASC"

Set DB = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
DB.Mode = adModeReadWrite
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("yourdb.mdb"))

rs.Open "SELECT FirstName, Surname, Extension FROM Users ORDER BY " & sort, DB

%>

<table>
<tr>
<td><b>First Name</b>
<a href="searchresults.asp?sort=FirstName DESC" title="Sort Descending">&darr;</a>
<a href="searchresults.asp?sort=FirstName ASC" title="Sort Ascending">&uarr;</a>
</td>
<td><b>Surname</b>
<a href="searchresults.asp?sort=Surname DESC" title="Sort Descending">&darr;</a>
<a href="searchresults.asp?sort=Surname ASC" title="Sort Ascending">&uarr;</a>
</td>
<td><b>Extension</b>
<a href="searchresults.asp?sort=Extension DESC" title="Sort Descending">&darr;</a>
<a href="searchresults.asp?sort=Extension ASC" title="Sort Ascending">&uarr;</a>
</td>

<%
Do While Not rs.EOF
%>

<tr><td><% = rs("FirstName") %></td></tr>
<tr><td><% = rs("Surname") %></td></tr>
<tr><td><% = rs("Extension") %></td></tr>

<%
rs.MoveNext
Loop
%>

</table>

<%
rs.Close
Set rs=Nothing
Set DB=Nothing
%>

 

[emozley]

Er... just realised there's some rogue </tr> tags in there!

Should read:

<tr><td><% = rs("FirstName") %></td>
<tr><td><% = rs("Surname") %></td>
<tr><td><% = rs("Extension") %></td></tr>

 

[emozley]

3rd time lucky:

<tr><td><% = rs("FirstName") %></td>
<td><% = rs("Surname") %></td>
<td><% = rs("Extension") %></td></tr>

 

[damber]

emozely,

Just in case that was a real life example, consider the possibility of SQL injection with the above code.

user only needs to enter this in a browser window to really mess up your system:

[URL unfurl="true"]http://www.example.com/searchresults.asp?sort=FirstName[/URL] DESC;DROP TABLE ORDERS;DROP TABLE USERS;--

You may not have the tables ORDERS and USERS, but the user can try as many variations of names as they please, until your system is broken, or they have necessary information - e.g. user/password details.

It may have just been an oversight in your example, but if you use this elsewhere, please secure the users input before using it in a SQL string.

Hope that helps

A smile is worth a thousand kind words. So smile, it's easy!

 

[emozley]

Certainly in the apps that I write if there is a search form I always turn ' into ''s and then pass that new search string to my SQL query. However dealing with SQL in a crafted URL is something I've not much experience in battling... can you recommend any decent threads on this?

cheers

 

[damber]

Well, the easiest way to combat sql injection for field inputs is to use stored procedures or parameterised queries (or both). Which involves setting the command string to the normal sql except placing @varname for each variable/parameter in the query, then using the paramters.append method or the parameters collection of the command to add the param values - so then they can have anything inside, and SQL will always treat them as parameter values, and not SQL commands.

If you are using the input to drive the actual sql command - i.e. not the parameter values, then use a [tt]select case[/tt] to verify that is acceptable - e.g.

Select Case request.querystring("sortorder")
  case "ASC"
    sSortOrder = "ASC"
  case else
    sSortOrder = "DESC"
end select

Or you can validate to see if the field name is in a list:

  if instr(1,"#fld1#fld2#fld78#", "#" & request.querystring("sortfield") & "#") > 0 then
   sSortField = request.querystring("sortfield")
else
   sSortField = "fld1"
end if

or swap and change.

It is also advisable not to use the real name of the field, as this will provide the user/attacker with additional information about your systems design - which you really don't want. I think you also can use the field index if your table structure remains constant (e.g. ORDER BY 2 DESC) though not sure if this is possible across all DB's.

do a search on google for "SQL Injection" and you'll get heaps of tutorials etc, that will go into more depth and specific defence techniques etc.

Hope that helps,

A smile is worth a thousand kind words. So smile, it's easy!