Cookie and SQL?

[Athmaus]

Hello!

I have two questions really.

Part I
On this website i am building it has password protection on certain pages. So i have a "login" portion on the right side of the page, that is shown on every page. When someone logs in is it possible to change that little section to just say Welcome so and so? Instead of having the username and password form with submit button?? Or is this something that requires some java to hide it?

Part II
When someone logs in i want them to recieve a cookie, in that cookie i want it to contain the person's name (wich i want the to be put next to the "welcome back on part I), email address, and up to four other numerical values. (some people will have greater access to sections of the site depending on who they are)


Is is possible to grab this information from the SQL database and put in the cookie? If so can anyone please show me, point me in right direction, ect? I greatlywould appreciate it.

Thanks!

[miranda]

Are you using classic ASP(ASP 3.0) or ASP.NET? in .NET just enclose the login into a panel and change the visibility of the panel to false and then display the welcome message.

In classic asp, use an if statement to check for login and if true then display your welcome message. Logging in is generally handled via a session variable(un written cookie) or a stored into a database table.

now as to cookies
Response.Cookies("cookieName")("field") writes the cookie out
Request.Cookies("cookieName")("field") gets the value written into the cookie.

say you name your cookie Oreo and you want to assign a value to the userName you would do that like so
Response.Cookies("Oreo")("userName") = "Jane Doe"
or if the user Jane Doe enters her name into a form field you would then enter the value into the cookie like so
Response.Cookies("Oreo")("userName") = Request.Form("userName")

Later on when you want to get the userName you would use
Response.Write (Request.Cookies("Oreo")("userName"))

which would then return the value of the cookie onto your page

[Athmaus]

I am having a problem with my session. I decieded, for now to just use sessiosn instead of a cookie.

WHen somoene logs in i want to pull their first and last name from my sql database and put in in the session. Here is my code for that


blnLoggedIn = True
myconn.execute("UPDATE login set logged = (logged + 1), lastlogged = '" & Now() & "' WHERE username='" & user & "' AND pass='" & pass & "';")

Set fname = fna.execute("SELECT firstname FROM login WHERE username='" & user & "' AND pass='" & pass & "';")
Set lname = lna.execute("SELECT lastname FROM login WHERE username='" & user & "' AND pass='" & pass & "';")

Session("login") = TRUE
Session("first") = fname
Session("last") = fname



When i try it, it gives me a "Missing Default Property" error. If i am setting fname and lname to session, first and last, shouldnt it work?? Do you know where my error might be?

[miranda]

I can help you if you include more of your code to work with. Start at the point where your select statement looks for the username and password before you assign blnLoggedIn = True

[Athmaus]

sorry, here you go:


<%
Response.Expires = -1000 'Make sure the browser doesnt cache this page
Response.Buffer = True 'enables our response.redirect to work

If Request.Form("valuepassed") ="true" Then
CheckLoginForm
Else
ShowLoginForm
End If

Sub CheckLoginForm
Dim myconn, blnLoggedIn, user, pass, exceeded, objRS, site, strdate, expired, lna, fna, fname, lname

Set myconn = Server.CreateObject("ADODB.Connection")
myconn.open = the connection string

Set fna = Server.CreateObject("ADODB.Connection")
fna.open = the connection string

Set lna = Server.CreateObject("ADODB.Connection")
lna.open = the connection string


user = Request.Form("username")
pass = Request.Form("password")
exceeded = 100
site = 1

Set objRS = myconn.execute("SELECT logged FROM login WHERE username='" & user & "' AND pass='" & pass & "';")

If objRS.EOF Then 'NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
blnLoggedIn = False
Response.Redirect "http://www.google.com"

Else 'LOGIN TO THE SITE
blnLoggedIn = True
myconn.execute("UPDATE login set logged = (logged + 1), lastlogged = '" & Now() & "' WHERE username='" & user & "' AND pass='" & pass & "';")

Set fname = fna.execute("SELECT firstname FROM login WHERE username='" & user & "' AND pass='" & pass & "';")
Set lname = lna.execute("SELECT lastname FROM login WHERE username='" & user & "' AND pass='" & pass & "';")

Session("login") = TRUE
Session("first") = fname
Session("last") = fname

Response.Redirect "http://ps2.ign.com"

objRS.Close
Set objRS= Nothing
myconn.Close
Set myconn= Nothing
fname.Close

fname.Close
Set fname= Nothing
fna.Close
Set fna= Nothing

lname.Close
Set lname= Nothing
lna.Close
Set lna= Nothing


ShowLoginForm
End If
End Sub
%>


here is my form

<% Sub ShowLoginForm %>
<table width="600" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td class="allblocks"><table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><h3><div align="center">Login Page</div></h3></td>
</tr>
<tr>
<td><p>&nbsp;</p>
<p>You maybe wondering why there is a login section of the site, and there are several reasons for this. If you want to know why feel free to email and ask me. </p>
<p>Please input your username and password below, and you will be able to access more sections of the site. </p></td>
</tr>
<tr>
<td><form name="form" action="index.asp" method="post">
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="3"><input type="hidden" name="valuepassed" value="true">
</td>
</tr>
<tr>
<td width="89"><p>User Name:</p></td>
<td width="178"><input name="username" type="text" id="username"></td>
<td width="333">&nbsp;</td>
</tr>
<tr>
<td><p>Password:</p></td>
<td><input type="password" name="password"></td>
<td><input type=submit value="Login" name="submit"></td>
</tr>
<tr>
<td colspan="3">&nbsp;</td>
</tr>
</table>
<% End Sub %> </form></td>
</tr>
</table></td>
</tr>
</table>

[miranda]

why do you need 3 connection objects??? That just consumes server memory for no need. 1 will suffice.

Also I didn't understand why you had a call to ShowLoginForm after the if statement had elevated to true and after you had done a redirect? you would be better off to put the call to ShowLoginForm where i have put it instead of the redirect

Try this code instead

Sub CheckLoginForm
Dim myconn, blnLoggedIn, user, pass, exceeded, objRS, site, strdate, expired

Set myconn = Server.CreateObject("ADODB.Connection")
myconn.open = the connection string

user = Request.Form("username")
pass = Request.Form("password")
'prevent sql interjection attacks with this code below
user = Replace(user, "'", "''")
user = Replace(user, ";", " ")
exceeded = 100
site = 1

Set objRS = myconn.execute("SELECT logged, firstname, lastname FROM login WHERE username='" & user & "' AND pass='" & pass & "';")

If objRS.EOF Then 'NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
blnLoggedIn = False
ShowLoginForm

Else 'LOGIN TO THE SITE
blnLoggedIn = True
myconn.execute("UPDATE login set logged = (logged + 1), lastlogged = '" & Now() & "' WHERE username='" & user & "' AND pass='" & pass & "';")

Session("login") = TRUE
Session("first") = objRS("firstname")
Session("last") = objRS("lastname")

objRS.Close
Set objRS= Nothing
myconn.Close
Set myconn= Nothing
Response.Redirect "http://ps2.ign.com" 'moved so that objects are closed and released
End If
End Sub

[BaldEagle]

I cannot add anything to miranda's explanation, but I thought you might be interested in reading this. It is about SQL injection atttacks. It will not probably not be an issue for 95%-99% of us but it is something to khow about and understand:

http://www.unixwiz.net/techtips/sql-injection.html

BaldEagle

[Athmaus]

Thank you that worked. I have another stupid quick question.

I am reading the first and last name and displaying on the site. How can make the Respone.Write statement stay within my CSS that i have set up? Also i am trying to make a link, (i have never response.write an active link in asp and havent been able to find a quick resource for it)


<%
If Session("login") = TRUE Then
Response.Write "Welcome Back "
Response.Write(Session("first")) & " "
Response.Write(Session("last"))
Else
Response.Write <a href="http://www.google.com">Click Here</a> to login</td>
End If
%>

[miranda]

Everything that is not a variable in response.write needs to be enclosed by double quotes. Now since you need to have double quotes around your url in the hyper link then you need to use 2 double quotes so that the single double quote will print out.

Response.Write "<a href=""http://www.google.com"">Click Here</a> to login</td>"