Solved

Check to see if IP is in Database Table SQL VBScript ACCESS

Posted on 2008-06-20
6
329 Views
Last Modified: 2008-06-24
Hi, this is very simple...

I am trying to find out if an IP address (that was saved in a table) is in a particular column called IP and if that is so...return a result

I just need the SQL to get it done

ADO connection here to whatever .mdb

i need the sql to determine whether or not the value is in the table or not...or at least how to find out ... evaluate and send a result bsed on that

i will take the result and then redirect all IP's in the list to a page prohibiting them from entering the site...

basically i just need a snipplet of how to check if the IP in already in the DB or not
0
Comment
Question by:Devario Johnson
6 Comments
 
LVL 3

Expert Comment

by:DarrenMcCall
ID: 21835512
Looking for something like this?
'Datebase connection

set oConn=Server.CreateObject("ADODB.Connection")

oConn.Provider="Microsoft.Jet.OLEDB.4.0"

oConn.Open "Database.mdb" 'CHANGE DATABASE NAME!

set oRS = Server.CreateObject("ADODB.recordset")

sql="SELECT IP FROM Table1" 'CHANGE TABLE NAME!

oRS.Open sql, oConn
 

If oRS.EOF Then

	'IP Not Found!

Else

	Response.Redirect("http://www.google.com/")

End If
 

'Cleanup

oRS = Nothing

oConn = Nothing

oRS.Close

oConn.Close

Open in new window

0
 
LVL 2

Expert Comment

by:jgoeders
ID: 21835650
dim rs as recordset
set rs =currentdb.openRecordset("SELECT * FROM IP WHERE IP_COLUMN = ' " & sIPVariable & " ' "
if rs.eof then
'not in table
else
'in table
end if
0
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 500 total points
ID: 21835688
DarrenMcCall,
- There is no condition in your query, so it will just check if there is anything in the table or not.
- If you do a redirect before the cleanup, the cleanup will not run.
- In the cleanup, you have to call Close before you set the references to Nothing.

This will work a little bit better:
' Declare variables

Dim objConnection, objRS, lngCnt, strIP

' Get IP

strIP = Request.ServerVariables("REMOTE_ADDR")

' Open connection

Set objConnection = Server.CreateObject("ADODB.Connection")

objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"

objConnection.Open "Database.mdb"

' Count occurances of the IP

strSQL="select count(*) from Blocked where IP = '" & Replace(strIP, "'","''") & "'"

Set objRS = objConnection.Execute(strSQL)

' Get the result

lngCnt = objRS(0)

' Close the recordset

objRS.Close

Set objRS = Nothing

' Close connection

objConnection.Close

Set objConnection = Nothing

' Check result

If lngCnt = 0 Then

        'IP Not Found

Else

        Response.Redirect "Denied.asp"

End If

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Expert Comment

by:DarrenMcCall
ID: 21837735
@GreenGhost, yes and yes. Nice catch, thanks.
0
 
LVL 5

Author Comment

by:Devario Johnson
ID: 21860361
Hi, guys, I got the concept from greenhost, and just wrote my own sql statement.  Thanks for all the help
0
 
LVL 5

Author Comment

by:Devario Johnson
ID: 21860835
this is the snipplet of code that I developed to achieve this task.  
Dim adoCon, ID, rsGuestbook, strSQL, banIP

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook_2003.mdb")

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

strSQL = "SELECT Banned.IP FROM Banned WHERE IP='" & Request.ServerVariables("REMOTE_ADDR") & "'"

rsGuestbook.Open strSQL, adoCon
 

Do While not rsGuestbook.EOF

	banIP = rsGuestbook("IP")

	if banIP <> "" then

	Response.Redirect "Denied.asp"

	end if

	rsGuestbook.MoveNext

loop
 

rsGuestbook.Close

Set rsGuestbook = Nothing

Set adoCon = Nothing

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now