Need some quick ASP/VB help =)

[HamSupLo]

I have an ASP page that is passed two variables: "strCat" for table name and "id" for article ID in the database. I want to display the contents of the database in a HTML table based on the two variables. I want to execute a SQL statment like this:

sqlstatement = SELECT * FROM strCat WHERE article_id = id

set rs=Server.CreateObject("ADODB.recordset")
rs.open sqlstatment

For the sql statment, can someone show me where to put the commas in the correct places? thanks.

 

[dighn]

commas? the only thing i see that is wrong is you haven't encosed the string in ""

try sqlstatement = "SELECT * FROM " & strCat & " WHERE article_id =" & id

assuming id is another variable and is not string
you don't enclose table names in '' right?

 

[whalen]

Something like this i believe...


sqlstatement = "SELECT * FROM '" & strCat & '"' & "WHERE article_id = '" & id

 

[whalen]

dighn,

I think strCat was a string variable, in which case you'll need to put single quotes around it. Assuming you converted your id variable to an integer, you wont need quotes around it.

 

[dighn]

Originally posted by: whalen
dighn,

I think strCat was a string variable, in which case you'll need to put single quotes around it. Assuming you converted your id variable to an integer, you wont need quotes around it.

yeah i forgot about it.

though from what i remember you don't enclose table names in ''


it's fixed

 

[HamSupLo]

hey thanks guys, i'll give it a try.

 

[HamSupLo]

Whalen, when i run the script with your statement, i get a syntax error. how do you know when to put those quotation marks where?

 

[whalen]

Hmm...maybe you dont need the single quotes around the table name...i was thinking you did since it was a string, but like dighn said, i dont think you do. I believe dighn's updated statement should work assuming the id variable is a number. If it is a string, you'll need single quotes around it.

 

[bunker]

No quotes around the table name. Use dighn's sql statement

Edit - Single quotes are only needed around a string when it's a value in a row you are searching for/inserting, etc....

 

[HamSupLo]

hey thanks guys, i removed the single quotes and it worked.

 

[Hector13]

I don't know what you are using this site for, but you may want to use a prepared statement for security reasons. It's been a while since I had to use ASP and SQL Server (thank god!), but I think somebody could pass in some nasty stuff into your id variable and screw your entire database.

 

[bunker]

Depends on how he's passing the variables. There's no problem if he's not passing them in the url.

Besides, all he's doing is calling an id number for an article to know what page to display so the only thing someone could do by passing their own variable is pull up a different page. It doesn't look to me like it's doing anything but a lookup.

 

[Hector13]

Originally posted by: bunker
Depends on how he's passing the variables. There's no problem if he's not passing them in the url.

if the variable is coming from the client, you are screwed whether you use GET or POST. If he is storing it server side (ie, session variables), you should be okay (though I remember IIS's session control being garbage).

Besides, all he's doing is calling an id number for an article to know what page to display so the only thing someone could do by passing their own variable is pull up a different page. It doesn't look to me like it's doing anything but a lookup.

If you are running any sort of "dynamic" sql statement on the sql server you could be exposing yourself to a lot of problems. It might look like a simple "lookup", but imagine if somebody passed in this value for the "id" variable (without the double quotes):

" '; TRUNCATE TABLE some_table_name -- "

in this case, the problem is even worse becuase it seems like the end user will have access to some of the table names in your database (it looks like that is what "strCat" is), so it would be simple to figure out valid tables to pass into the above "query".

 

[GiLtY]

Nothing to say except for future reference, please post this in the Programming forum, it's there for a reason

Nothing harsh, just a friendly reminder

 

[HamSupLo]

Can you show me some pointers on how to tighten up security? I'm a web programming noob.

I'm using the SQL query to display a webpage that i want to update from a specific table. That's why i'm passing the "strCat" variable to select a certain table and then the ID variable to indicate which page in that table i want. Once i pull the page, i'm sending the output to a form which i can then make updates and submit back into the database. I'm accessing the update and edit page through a private part of the site.

can you explain to me a little about using session variables?

I know it should belong in the programming forum, but it's kinda dead there. Seems like all the programmers want to post in here

 

[Hector13]

Originally posted by: jooksing
Can you show me some pointers on how to tighten up security? I'm a web programming noob.

well, like I said before, I haven't done much asp or web programming lately, but the most important thig is never trust any input from your user.

Make sure you validate anything that is submitted to your webpage. And make sure you do it on the server! Client-sice validation (ie, javascript or some such) is useless and will not protect you from anything.

As for session variables, they are basically a way to data about a users "session" on the server. Instead of having variables passed around through GET (on the querystring) or POST methods, both of wich give you no security, IIS (or whataver web server you use) can store info about each user for you. Sessions typically have a time-out (around 20 minutes or so) so that you don't need to worry about doing any "garbage collecting" of whatever data you store. In other words, if your user doesn't use the website for 20 minutes, IIS will remove his "session variables" (ie, log him out).