hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0
Set strOrder = Request.QueryString("Order")
oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>
</head>
<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
make = oRS("make")
model = oRS("model")
price = oRS("price")
%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop
oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>well you could use a bubble sort to handle the sort in the app code (where presentation issues should be handled) or you could index your order by column.|||thanks, urm...what's a bubble sort and how do I do it?
and how do I create an index?
the columns to sort by are text and updated regularly by the CMS.
I have Googled indexing SQL Server and cursor types, but all the stuff I find assumes an already high level of understanding which is really annoying (the opposite of a Microsoft help file which states the obvious - "this a whim whom slurping valve, use it to slurp whim whoms...") sorry, I digress.|||Yeah? Go read the IBM or Oracle Manuals....
In any case, are we talking about SQL Server in the first place?
Do you have a dba or are you "it"
And why aren't you using stored procedures?|||This nifty asp you've written is wide open to sql injection. Do you know what that is?|||Oh God, Oracle manuals are the worst...|||I think the bubble sort thing was a little joke by Sean that went over your head. :)
bubble sort is a sorting algorithm, and one of the least efficient ones. it's a CS 101 kind of algorithm. search for it on google, but don't use it. In fact most pages you find that describe it should tell you not to use it.|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.
Brett, what do you mean by "Do you have a dba or are you "it""
Feel free to direct me in the right direction|||Who administers your database?
Like, who creates tables for eaxmple?|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html
Gist is - if you concatenate strings from user input and submit to the database for execution and you are wide open to SQL Injection. Parameterise your data accces (don't try cleaning your strings).|||Parameterise your data accces (don't try cleaning your strings).
Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...|||Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...
Oh yeah...like what?|||replacing apostrophes, equals signs, asterix' and percentages...
Eg. replace(<fieldname>,"'","")|||Great..parse this
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)|||Sounds like a problem around tempdb. Query the waittype value in the sysprocesses table, to see what this is hanging up on. Your tempdb is not set to autoshrink, is it?|||DECLARE @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.x varchar(8000) '
SELECT @.sql = @.sql + 'SET @.x =
CHAR(68)
+CHAR(82)
+CHAR(79)
+CHAR(80)
+CHAR(32)
+CHAR(68)
+CHAR(65)
+CHAR(84)
+CHAR(65)
+CHAR(66)
+CHAR(65)
+CHAR(83)
+CHAR(69)
+CHAR(32)
+CHAR(78)
+CHAR(111)
+CHAR(114)
+CHAR(116)
+CHAR(104)
+CHAR(119)
+CHAR(105)
+CHAR(110)
+CHAR(100)
SELECT @.x'
EXEC(@.sql)|||suffice it to say that scrubbing strings will address only the most elementary injection attacks.
the real fix is: NEVER execute sql that was pasted together based on user input. if you do this, you are vulnerable no matter how tricky your parsing code is.|||Great..parse this
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
Ahh clever... I did say I was using access but I never considered this|||Great..parse this
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
actually, that statement would probably fail because of the GO. :)
remove the GO and you are in business.|||I think the bubble sort thing was a little joke by Sean that went over your head. :)
you guys ruin all of my fun. i am too busy these days to lead the posters around by the nose for several posts anyways.|||Haha I remember learning (not to) use bubble sorts 2 years ago... Man those were long lessons!
Imagine your old, grey, chunky, droning computing teacher mumbling on for an hour and a half just to turn round and go "..and that's why you won't ever use this" >:(|||bubble sort is actually ok if you only have a few items to sort. in practice even the most hairbrained algorithm will be fast if there are only 10 items.
as soon as you have 100k to sort though, your code will fall over if you use it.|||Thanks jezemine
is it likely that my small database of 30 or 40 cars with descriptions and specs will slow to the point of timeouts when I ask it to order by 'price' for instance?
my DB seems to have suddenly done this, could it be thatthe amount of data has suddenly broken some threshold? or as I suspect, could 1and1 have changed my DB at a low level and cripled its sort capabilty. It was fine last week.
NB: please keep it simple, all this archane terminology is just making matters worse for my little brain!|||I don't think so...
http://www.autotrader.com/fyc/searchresults.jsp?num_records=&search_type=both&distance=10&address=07052&style_flag=1&make=ASTON&model=&make2=&start_year=1981&end_year=2008&min_price=&max_price=&transmission=&engine=&drive=&doors=&fuel=&max_mileage=&color=&keywords_display=&sort_type=priceDESC&body_code=0&certified=&advanced=&default_sort=priceDESC&keywordsrep=&keywordsfyc=|||actually, that statement would probably fail because of the GO. :)
remove the GO and you are in business.
Ummm, no
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)|||I think you can always substitute a semicolon for the GO, anyway.|||Guys...what are you talking about?
what does this do?
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)
- is this part of the thread or are you off on a tangent?|||Ummm, no
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)
I stand corrected!
I was under the impression that "GO" was only understood by the client tools like osql/sqlcmd/QA as a batch separator. apparently it's also understood by EXEC and sp_executesql.|||Guys...what are you talking about?
what does this do?
- is this part of the thread or are you off on a tangent?
They are off on a semi-tangent, regarding whether the code you are using is safe from SQL Injection attacks.|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...
Ummmm...you...will be...you will...be...
In any case there are ways...but you need control, you must learn control
Use stored procs access only and you'll be fine|||a poorly written proc is just as vulnerable to sql injection. consider this proc:
create proc ExecSql (@.sql varchar(max)) as exec(@.sql)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment