| Author |
Topic |
|
malinkacc
Starting Member
4 Posts |
 Posted - 2010-01-26 : 21:20:34
|
| I am very new to sql world, I have this sql code and it works just fine:sql = "select count(city) as cx from NY where CITY='New York'"set rs=conn.execute(sql)response.write(rs("cx"))The above counts all records with New York city and returns count.I have been trying to put this on a dynamic .asp page and modify 1st line to grab city field from the page that it is being viewed on and make it dynamic, I have a field for city which I was trying to place in spot of "New York", it is being called by <% response.write x_city %> ont the page right now, can anybody help? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-26 : 22:17:06
|
| [code]sql = "select count(city) as cx from NY where CITY='" & x_city & "'"[/code]Make certain that you escape single quotes. Also disallow special characters like ; % _Read this article and understand it. This is VERY IMPORTANT! http://msdn.microsoft.com/en-us/library/ms161953.aspx |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 04:03:12
|
The article Russell points to is criticalHowever, there is no need to disallow special characters if you escape any embedded single quotes.sql = "select count(city) as cx from NY where CITY='" & replace(Trim(x_city), "'", "''") & "'" better to encapsulate that in a function, so you can reuse it wherever you need to put User-supplied data into a SQL statement. Here's my function which does a bit more - like testing the parameter for NULL value, and trimming any spaces:Function SQLStr(ByVal sStr)if isnull(sStr) then sStr = ""if IsNull(sStr) then sStr = ""elseif IsEmpty(sStr) then sStr = ""else sStr = Trim(CStr(sStr))end ifSQLStr = "'"& replace(sStr, "'", "''") & "'"End Function then you can dosql = "select count(city) as cx from NY where CITY=" & SQLStr(x_city) use this for numbers as well (yes, I know, SQL will then implicitly cast them, but it is easy and safe to implement)One caveat: Do NOT rely on this, alone, for any data you pass to something else (e.g. a Stored Procedure) that ALSO creates dynamic SQL |
|
|
|
malinkacc
Starting Member
4 Posts |
Posted - 2010-01-27 : 15:22:17
|
| Wow, thank you russell and kristen,it works!!!I also have another question (please let me know if I should remove this here and start a new topic)We also have another field called "viewhits" - it's just a counter that increments by 1 each time user views page.How would I go about inserting sql to find top 5 viewed pages (by id) and list them with a link to each page? Thank you again for all help, this place is awesome.p.s. Forgot to add, thank you for mentioning the sql injection part, very important, I think for now I will let it slide as ALL data is inserted manually by human and there is no auto add feature available. thanks again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 15:54:23
|
"I think for now I will let it slide"The problem is not the data, but the query parameter that you get from the user.Your query has CITY='New York' and the "New York" bit comes from the user. I can guarantee to you that I can hack whatever web page you give me to choose "New York" so that I send you some injected nastiness that will delete all your data, drop the database, and spoil you day in all sorts of ways  However, regardless of that  , you still have the issue that the data from the user might need to include a single quote, so you need to pass the parameter through the function I gave above (or one like it), and that will make the SQL Inject problem go away (with the caveats I gave earlier) - provided you are religious and use the function for every piece of data (that came from a user) that you send to SQL Server."How would I go about inserting sql to find top 5 viewed pages (by id) and list them with a link to each page? "Probably something like this:SELECT TOP 5 Col1, Col2, ...FROM MyPagesTableORDER BY viewhits DESC You create the "link" in your application. Not sure what your URLs are like, and what data you have in your MyPagesTable, but lets assume you have a column "PageURL" and that contains something like "FooBar.asp", and a column "PageName" that contains the name of the page.In you app you need to generate"<a href='/" & rs("PageURL") & "'>" & rs("PageName") & "</a>" |
|
|
|
malinkacc
Starting Member
4 Posts |
Posted - 2010-01-27 : 19:47:10
|
| Kristen, thank you for all your help.I did this:<% sql = "SELECT TOP 5 fHits as cx FROM NY ORDER BY fHits DESC" set rs=conn.execute(sql) response.write(rs("cx")) rs.Close Set rs = Nothing %>All it does it just displays the number of a page that has most views, 90 for example. How do I tell it to actually spit out the top 5 records, ie, show company name, city, address, or whatever else I choose to show.I think I did mention before, this is all new to me. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-27 : 20:03:47
|
| [code]<%sql = "SELECT TOP 5 fHits as cx FROM NY ORDER BY fHits DESC"set rs=conn.execute(sql)While Not rs.EOF response.write(rs("cx")) & "<br>" rs.movenextWendrs.CloseSet rs = Nothing %>[/code] |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 02:58:40
|
| "show company name, city, address, or whatever else I choose to show"Add those columns to the SELECT statement, and to your display logic.You need to get a book, or a training course, on the basics. You won't learn much from the brief answers you get on forums, and folk here won't understand all the issues you are struggling with, but probably not articulating. |
|
|
|
malinkacc
Starting Member
4 Posts |
Posted - 2010-01-28 : 16:59:12
|
| Thank you guys for all the help so far, I think I will chill out for now as I can't figure out how to make it spitt out more fields, like I mentioned before this is all new to me.Hey Kristen, just bought the book (2 weeks for delivery), hopefully I'll leave you people alone.Thank you for everything again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 02:27:40
|
| "hopefully I'll leave you people alone"Don't do that! But it really isn't viable to help you with first steps. Come back when you have some specific questions. |
|
|
|
|
Sql count modification