Right, heres my dilemma.

A company contacted my class asking us to fix some "little security" issues conserning about 60 or so websites. Its written in ASP.net and deals with SQL server. Their version of building a website on the fly consists of something similar to:
{The following snippit logs into the server and gets the information out of a database and loads it into the browser. Please note the server address and PASSWORD!!!! would be displayed if someone innocently viewed the source code.}

This little snippit is viewable by looking at the page source and all of their websites are like this. There are a few more similar to this but those just deal with the other "less important" tables. Needless to say, they got hacked. BIG-TIME! They came to us and asked us to change this by using stored procedures on the server as opposed to the viewable files. That unfortuneately entales looking through all the flies and changing every little .open containing a SQL command to a executable stored procedure command.

Selecting items out of the database is easy enough, but passing in variables and injection statements seem to trip us up; my partner and I managed (just barely) to throw together a shaky injection statement so that the database holding all visitor information and form content isn't touchable (we think). Our extent of ASP.net was thrown at us last year and we took a week to do some basic website forming stuff. And now they are asking for IF...THEN statements and a few other things that I can't seem to guess my way around. The websites are live and there is a little pressure because there is an internship for a few kids hanging in the balance, plus the current relationship with the company for future interns. It tedious and exausting work, I have racked my brain trying to think a way to make it work, but I just can't get the hang of it.

Also, I want to make the stored procedures as reuseable as possible, but their original coding of what parameters determine what content is supposed to be displayed often times make that impossible with out reworking the hundreds of thousands of tables that are already set up. I guess what I need is for someone with the time to explain exactly how variables from ASP.net code to SQL stored procedures. Its just so much for me to wrap my little pea-brain around at one time.....(those ' ", &var are making my head spin.....) They want us to turn:

Into something else......? It seems to be picky about spacing and I am not totally sure what it is doing with the values(....) part. Then theres the '","' buisness and &s. I am so confused.

If someone would care to help, that would be great.

Well I guess the best way to help you is first describe that query they are using and then provide you a nice site which can introduce you to using stored procedures. I am not sure which language you are using for ASP.NET, but the example page uses C#. The same method and a lot of the same syntax works for VB.NET etc.

So on to your query...



First thing to notice is the tbl.open at the beginning. This is what is called "opening" a recordset on the table. You can think of it as simply executing that insert query on the table. The second thing to notice is that you have an insert query that will insert a record to the errorlog table. That tables columns are website, pagename, errormsg, errordate, and fixflag. That is five columns and the values section next will have five values to put in each of those columns. They match up, are the same type that the column expects and thus in the same order.

So for instance, the value returned by request.servervariables("http_host") will return a hostname, preferably string data and it matches up with the first column "website". The filename parameter in values is matched up with the "pagename" column, the text value is matched up with "errormsg" column etc etc. Fixflag would then store 0.

Each of these values are variables, so they cannot be put straight into the query string, they are concatenated on. That is what all the ampersands are about. So if the value returned from request.servervariables("http_host") was "www.google.com" then it would append that to the string leading to....

...) values('www.google.com',...

The next one being filename variable. If that was set to "homepage.aspx" then it would give you...

...) values('www.google.com','homepage.aspx',...

The google url would be put in the "website" column and homepage.aspx would be put in the "pagename" column etc.

So at the end of this all you will have something that may look like this...



Make sense? The last values you see there, next1 and 2 are part of the tbl.open statement. They are parameters to tell how to open up the recordset using the connection object (which is probably next1 there) and the 3 is the cursortype.


Now for the stored procedures, the site below will give you a good start on how to create the stored procedure for the insert and how to use ASP to then execute it, passing it the parameters you pass up in that open statement (eg www.google.com, homepage.asp, etc). A word of caution, keep track of the parameters types. For instance the google.com is a varchar, but the 0 is an integer. Make sure they match the database data types.

Hope this site helps you out...

Using Stored Procedures with ASP.NET

Enjoy!

"At DIC we be stored procedures.... of ass kicking destruction!"

Yes, this helps a ton, thank you so much!

My teacher eventually got around to contacting the company, and they actually wanted it to look more like:

or like


I guess they wanted it to be more secure, and harder to figure out how to get into the databases... But thank you sooooo much, it made it less complicated.

Alil, the correct way of doing this is to place the database configuration inside the web.config file and no one can ever see it unless they have access to the actual web server. As far as stored procedure calls, I would build classes or DAL's that would contain all the database logic inside of it, an easier way would be use CodeSmith or just Enterprise Libraries.

Then you could use something like


Either approach, never, ever put the Stored Procedure names or account information anywhere that the page is being downloaded and viewed.

Personally, I love using CodeSmith (http://www.codesmithtools.com) because it is easy to use and implement and cuts database programming down to a few hours rather than days.

Just my two cents.

This post has been edited by phatSolutions: 28 Jan, 2008 - 11:58 AM

Thanks. We eventually figured out the problem. Its just time-consuming and what not. But yeah.