Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP and SQL

Reply
Thread Tools

ASP and SQL

 
 
Athmaus
Guest
Posts: n/a
 
      06-02-2005
I have this login script for a certain portion of our website for a while,
and have around ~7500 users in a database that the script accesses.

I added two new columns in this table, the reason for this is because i
found a new trick for adding more security for the section of the site that
this script is protecting. Problem is that now that I have added thee two new
colums, the script does not write in any information in these two columns.

I have copied the script and made a test table and everythign works, and the
infromation is added in those two new columns.

Does anyone know what might be preventing the sciprt from writing in these
tables? Or can you not add columns to a SQL table once it is in use (and that
would make no sense to me if it was like that)

I wish i could provide more information other than posting up the script, as
i get no errors at all.

Any help would be greatly appreciated. Thanks!
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-02-2005
Athmaus wrote:
> I have this login script for a certain portion of our website for a
> while, and have around ~7500 users in a database that the script
> accesses.
>
> I added two new columns in this table, the reason for this is because
> i found a new trick for adding more security for the section of the
> site that this script is protecting. Problem is that now that I have
> added thee two new colums, the script does not write in any
> information in these two columns.
>
> I have copied the script and made a test table and everythign works,
> and the infromation is added in those two new columns.
>
>
> I wish i could provide more information other than posting up the
> script, as i get no errors at all.
>
> Any help would be greatly appreciated. Thanks!


At least post the portion of the script that is supposed to write the
information to the database. (we do not need to see any html - we only need
to see the vbscript code that performs the data insertion)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
 
 
 
Athmaus
Guest
Posts: n/a
 
      06-02-2005
Here is the code, it works on a fresh database that i setup, but it is not
working on the already established database wehre i put 2 new columns in




If Session("login") = FALSE Then
Response.Redirect "http://www.yahoo.com"
Else

Dim myconn, verify, blnLoggedIn, user, pass, site, logged, objRS, exceeded
Set verify = Server.CreateObject("ADODB.Connection")
verify.open = "connection string"
Set myconn = Server.CreateObject("ADODB.Connection")
myconn.open = "connection string"

'Response.Write(Session("username"))
'Response.Write(Session("password"))

user = CStr(Session("username"))
pass = CStr(Session("password"))

exceeded = 5

Set objRS = myconn.execute("SELECT id, download, totaldl FROM regfreeup
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.yahoo.com"

Else
If objRS("download") >= exceeded Then 'LOGGED IN AN ABNORMAL TIME
blnLoggedIn = false
Response.Redirect "http://www.google.com"

Else '''EVERYTHING PASSED PROCEEDE WITH DOWNLOAD
blnLoggedIn = True
verify.execute("UPDATE regfreeup set download = (download + 1) , totaldl
= (totaldl + 1) WHERE username='" & user & "' AND pass='" & pass & "';")

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

objRS.Close
Set objRS= Nothing
myconn.Close
Set myconn= Nothing
verify.Close
Set verify= Nothing

End If
End If
End If

"Bob Barrows [MVP]" wrote:

> Athmaus wrote:
> > I have this login script for a certain portion of our website for a
> > while, and have around ~7500 users in a database that the script
> > accesses.
> >
> > I added two new columns in this table, the reason for this is because
> > i found a new trick for adding more security for the section of the
> > site that this script is protecting. Problem is that now that I have
> > added thee two new colums, the script does not write in any
> > information in these two columns.
> >
> > I have copied the script and made a test table and everythign works,
> > and the infromation is added in those two new columns.
> >
> >
> > I wish i could provide more information other than posting up the
> > script, as i get no errors at all.
> >
> > Any help would be greatly appreciated. Thanks!

>
> At least post the portion of the script that is supposed to write the
> information to the database. (we do not need to see any html - we only need
> to see the vbscript code that performs the data insertion)
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-02-2005
Athmaus wrote:
> Here is the code, it works on a fresh database that i setup, but it
> is not working on the already established database wehre i put 2 new
> columns in
>
>
>
>
> If Session("login") = FALSE Then
> Response.Redirect "http://www.yahoo.com"
> Else
>
> Dim myconn, verify, blnLoggedIn, user, pass, site, logged, objRS,
> exceeded Set verify = Server.CreateObject("ADODB.Connection")
> verify.open = "connection string"


Hopefully you are using a sqloledb connection string ...
http://www.aspfaq.com/show.asp?id=2126

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


Why two connection objects? Are these separate database servers? If not,
only one connection is needed. Don't be wasteful of your network and server
rewources.

>
> 'Response.Write(Session("username"))
> 'Response.Write(Session("password"))
>
> user = CStr(Session("username"))
> pass = CStr(Session("password"))
>
> exceeded = 5
>
> Set objRS = myconn.execute("SELECT id, download, totaldl FROM
> regfreeup WHERE username='" & user & "' AND pass='" & pass & "';")
>
> If objRS.EOF Then '''NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
> blnLoggedIn = False


Bad technique here. Always close and destroy your ADO objects when finished
with them. The lines of code appearing after a redirect will NOT get
executed.

> Response.Redirect "http://www.yahoo.com"
>
> Else
> If objRS("download") >= exceeded Then 'LOGGED IN AN ABNORMAL TIME
> blnLoggedIn = false
> Response.Redirect "http://www.google.com"
>
> Else '''EVERYTHING PASSED PROCEEDE WITH DOWNLOAD
> blnLoggedIn = True
> verify.execute("UPDATE regfreeup set download = (download + 1) ,
> totaldl = (totaldl + 1) WHERE username='" & user & "' AND pass='" &
> pass & "';")
>
> Response.Redirect "http://www.ps2.ign.com"
>
> objRS.Close
> Set objRS= Nothing
> myconn.Close
> Set myconn= Nothing
> verify.Close
> Set verify= Nothing
>
> End If
> End If
> End If


My recommendations:
1. to facilitate debugging, comment out the redirects
2. Insert some response.write statements so you can follow the execution of
the code.
3. When using dynamic sql, assign your sql statements to variables so they
can be written to response for debugging
4. Use indenting
5. Use parameters
6. Use stored procedures to minimize the trips to the database

Here is how I would rewrite this code:

I would first create a stored procedure on your server, like this:

CREATE PROCEDURE VerifyUser (
@user varchar(50),
@pass varchar(50),
@limit int) AS
IF NOT EXISTS (SELECT * FROM regfreeup WHERE
username= @user AND pass = @pass)
RETURN 1
DECLARE @downloads int
SET @downloads = (SELECT download FROM regfreeup
WHERE username= @user AND pass = @pass)
IF @downloads > @limit
RETURN 2
UPDATE regfreeup set download = (download + 1) ,
totaldl= (totaldl + 1)
WHERE username= @user AND pass = @pass
IF @@ERROR =0
RETURN 0
ELSE
RETURN 3


Then, in ASP, I would use a Command object as follows

<%
Dim myconn, retVal, user, pass, site, logged, exceeded
dim sURL

If Session("login") = FALSE Then
sURL = "http://www.yahoo.com"
Response.Write "Not Logged In. <BR>"
Else
Set myconn = CreateObject("ADODB.Connection")
myconn.open = "connection string"
user = CStr(Session("username"))
pass = CStr(Session("password"))
exceeded = 5

set cmd=createobject("adodb.command")
arParms = array(user,pass)
cmd.commandtext="VerifyUser"
cmd.ActiveConnection = myconn
set params = cmd.Parameters
params.append cmd.CreateParameter("RETURN_VALUE", _
3,4)
params.append cmd.CreateParameter("@user", _
200,1,50,user)
params.append cmd.CreateParameter("@pass", _
200,1,50,pass)
params.append cmd.CreateParameter("@limit", _
3,1,,exceeded)
cmd.Execute ,,129
retVal = params(0).value
select case retVal
case 0
sURL="http://www.ps2.ign.com"
Response.Write "No problems. <BR>"
case 1
sURL = "http://www.yahoo.com"
Response.Write "No problems. <BR>"
case 2
sURL = "http://www.google.com"
Response.Write "Improper login. <BR>"
case 3
sURL = "http://www.microsoft.com"
Response.Write "The update failed. <BR>"
end select
set params=nothing
set cmd=nothing
myconn.close: set myconn=nothing
End If
Response.Write "Redirecting to " &
Server.htmlencode(sURL)
'Response.Redirect sURL
%>


When finished debugging, comment out the response.writes and uncomment the
redirect.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
MS Access SQL > ASP SQL problem.... david@scene-double.co.uk ASP General 10 01-06-2005 12:23 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
ASP SQL - using variables in SQL select screen Ed Garcia ASP General 4 08-07-2003 07:41 PM



Advertisments