ASP Help?

azzeretti

Before I start I need to say that I am no programmer but got landed with this, so aplogise in advance.

I need to create a type of Authentication page to a website. I have an Access db in the back with one table with a list of usernames. The login page is a form which posts to the auth.asp. The problem I have is that, for various reasons, I am only interested in the first 5 digits from the username posted in the login.asp page. However, the actual usernames will be 15 digits in lenght and I need to make the users log in with their full username (so making the login text box 5 chars in lenght is out of the question)
Does anyone know how to MID the lenght of a Reponse.Form("Username") feild before passing it into my SQL statement?

E.g
Login.asp

---

> Username: 1234567891012131
auth.asp

---

> StrSQL ="Select * from db.name where user like (MID????)'" & Request.Form("UserName")

Cheers

The Corinthian

sUsername = Reponse.Form("Username")
If Len(sUsername) > 5 Then sUsername = Left(sUsername, 5)

azzeretti

Thanks for this, but where do I set this? Before the SQL query? Do I then just sub this in the statement like:
SELECT * From db.tbl where user# like ' " & sUsername & " ' "

ShayHT

If the user is typing in all 15 chars then why not use the whole string?

SQL = "SELECT * FROM Table_Name WHERE Username = '" & Username & "' AND Password = '" & Password & "' "

azzeretti

ShayHT wrote:

If the user is typing in all 15 chars then why not use the whole string?

SQL = "SELECT * FROM Table_Name WHERE Username = '" & Username & "' AND Password = '" & Password & "' "

Not sure I understand this, I have no password feild. I only want to authenticate by user#. I have 32 locations with 275,000 users. Each location has a unique 5 digit prefix and the last digits are assiged on a per user basis. The database are sperate and although I have access to all of them they are all on different sites throughout the world. Instead of trying to authenticate to all of them via the unique username I figure it would be easier to take the 32 unique 5 digit prefaces and put them into a table, then make each user put in their full username - this stops other users chancing the login.
BTW, the data on the back end is not that sensitive, we just want some small deterant for non-employees etc.

The Corinthian

I suggest you read up this sample tutorial on ASP and databases and work from there.

azzeretti

Thanks.
I am little beyond that level, thanks for the politness though.

The Corinthian

azzeretti wrote:

I am little beyond that level, thanks for the politness though.

All right then. I’ll assume you can handle a database connection and SQL select query in ASP. I’ll also assume that you’re putting those 5-digit prefixes in a table. Given this, all you’ll need is:

sSQL = "SELECT * FROM Table_Name WHERE Username = '" & sUsername & "'"

All you’ll get back, however, is the content of that row (or nothing if there’s no match, i.e. oRS.EOF is True, where oRS is your recordset). Is this what you’re looking for or is there more?

azzeretti

No, that is pretty much it. I have manage to open the connection and I can return rows that meet certain conditions, its just that part where I want to pass the truncated string from the Form into the query. Its the part in bold below I am having trouble with.
Heres my code:

<%
'Dimension variables
Dim conn
Dim RS
Dim strSQL

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("auth.mdb")

Set RS = Server.CreateObject("ADODB.Recordset")

sUsername = Reponse.Form("txtFirstName")
If Len(sUsername) > 5 Then sUsername = Left(sUsername, 5)

strSQL = "SELECT * From auth.card where cardno like ' " & sUsername & " ' "

RS.Open strSQL, conn

If RS.EOF Then
Response.Redirect ("login.asp")
Else
Response.Redirect ("yourin.asp")
End If

%>

The Corinthian

What trouble? Explain what's not working.

Try writing strSQL to the buffer to see if it's concatonating correctly.

Seth Brundle

azzeretti wrote:

Thanks.
I am little beyond that level, thanks for the politness though.

No offence but are you sure?
You are having trouble with a simple VBScript if statement consisting of two functions (Left & len).
Your SQL also leaves you open to SQL injection.

The Corinthian

kbannon wrote:

Your SQL also leaves you open to SQL injection.

Pedantic point, but it's the VBScript rather than the SQL that is leaving him open to SQL injection.

Seth Brundle

I was actually going to write "Your SQL also leaves you open to SQL injection." but thought that may be too confusing

azzeretti

kbannon wrote:

No offence but are you sure?
You are having trouble with a simple VBScript if statement consisting of two functions (Left & len).
Your SQL also leaves you open to SQL injection.

Well, if we are being pedantic here, did you look at the link?

Also, I'm not concerned with SQL Injection for many, many reasons (on this particular box).
Thanks

Seth Brundle

azzeretti wrote:

Well, if we are being pedantic here, did you look at the link?

Also, I'm not concerned with SQL Injection for many, many reasons (on this particular box).
Thanks

I did and what? I don't think that I need to read up on how to build an SQL statement on the fly - however, you were the one looking for the advice. You got it and still couldn't manage it

I also offered a tip on making your code more secure - am I to know that you have many many reasons for not needing my advice?

azzeretti

Thanks for you help.
I am not a programmer as I stated at the start. I am a DBA and I have a good grasp of security issues. This project is an exercise in basic asp scripting, I am trying to learn. The link I was provided with, as far as I could see, was an very basic introduction to asp, which I believe I had got past (as I can connected to the db and perform a search, edit and delete recoreds via asp) I had already being using aspfree.com to get this far. I got stuck on a particular matter and asked for advice on that. You pointed out that I needed help with something else entirely. Obviously, we all know your capabilities, but it must be comforting to know that I won't be back here looking for it.

"I don't think that I need to read up on how to build an SQL statement on the
fly"
I have no problem building any sort of SQL queries, "on the fly" or otherswise.

rsynnott

Er, why are you not concerned about SQL injection? Unless you're the only one going to have access to the form with the login box, you really do need to be.

EDIT, just saw this:

azzeretti wrote:

Thanks for you help.
I am not a programmer as I stated at the start. I am a DBA and I have a good grasp of security issues. This project is an exercise in basic asp scripting, I am trying to learn.

Okay, you may as well start as you mean to go on, though. This code could never be used in a deployed system; there's no point getting into bad habits.

As an aside, you might be better off learning ASP.NET or something in this day and age.