Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Johnhamman
Starting Member
37 Posts |
 Posted - 2002-04-04 : 14:46:47
|
| Hi all, This probably isn't challenging but for a beginer like me its hitting me pretty hard. I am creating a .NET class that recieves 3 items: the users IP address, usersDNS and the users User_agent. I would like to perform certian functions based on what i find when i compare this information to the database.In my database i have 3 tables: BadIPs, GoodUA,GOODIPsIn the BadIPs table I have these fields : <pre>ID int, IP nvarchar(15), DNS nvarchar(255), UsrAgnt nvarchar(255), name nvarchar(50)</pre>In the GoodIPs table I have these Fields: <pre>ID int, IP nvarchar(15), DNS nvarchar(255)</pre>And Finally i have in the GoodUA table these fields: <pre>ID int, UA nvarchar(255)</pre>Basicly i need to create a stored proc that1. checks the goodtables for matching fields, then if returns a match,returns a value for "good" and then skips the next step.2. if no match is made in step 1 then is searches the Bad table for matches in ip,dns,and usragnt field. If match is made it returns a value for "bad" with also the 'Name"value found. 3. if no match is found in step 2 then it returns a value for "good" and closes.Personaly Im stuck. I can see a couple of ways to go about this but i have no clue on how to do this in the fastest method! I am using SQL Server 7 with the most uptodate service pack on a win2k service pack 2 machine. Im not even sure if i made up the tables the most effeciant way either. Any help would be appreciated. the Bad list is about 8000 rows large and the good tables only about 300 rows each.John Hamman |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-04 : 15:15:57
|
| First, if you're using .NET then I'll hazard a guess that you have addresses stored in IPAddress structs. If so, it's by far more efficient to store and search on the (long) IPAddress value then the string value. You would use the int type for an IP address vs. varchar(15) ( actually you have nvarchar(15), which specifies a unicode value, which you don't need and takes twice the space ). In general, don't use the unicode types ( those with the 'n' prefix ) unless you have a specific international need.Second, the logic you wrote can be simplified to be:if there does not exist a matching row in the 'bad' tables, return 'bad' else 'good'.if not exists (select 1from BadIPswhere IP = @IP and DNS = @DNS and UsrAgnt = @UA)return 'bad'else return 'good'setBasedIsTheTruepath<O> |
 |
|
|
Johnhamman
Starting Member
37 Posts |
Posted - 2002-04-04 : 15:46:03
|
| awsome, I have had sutch a difficult time figuring out how to correctly design tables. It was easier in access but as we all know, access is nothing compared to SQL server. I have read the bookonline but still get lost. Guess im half dumb or whatnot. and you were right i am using the ipaddress value. Thanks for the tip off. Also would the int field have a problem with the '.' in the ip? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-04 : 16:14:32
|
quote:
Also would the int field have a problem with the '.' in the ip?
That's the thing - an IP address is a four byte value. So is a SQL Server int. The '.' in the IP address is the string representation. An IP address of 10.10.10.50 = 168430130, for example.setBasedIsTheTruepath<O> |
|
|
|
Johnhamman
Starting Member
37 Posts |
Posted - 2002-04-04 : 16:46:55
|
| I think i understand. But now i have a problem I have in the table already the ips like 10.2.35.21 and i dont know how to convert them to int. since it wont let me!any idea on how to do that?john |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-04 : 17:13:03
|
Actually, int won't work because it's a signed integer value (negative and positive), if you tried to convert an IP address where the first octet was more than 127 (like 201.14.3.213), it will throw an error in SQL Server because it would exceed the capacity of the value (+2 billion something)In fact, unless you are absolutely squeezed for space, leave the IP address as a varchar type. If you need to search a particular octet, you can use the PARSENAME function:SELECT PARSENAME('101.202.30.40', 4) --returns 101SELECT PARSENAME('101.202.30.40', 3) --returns 202SELECT PARSENAME('101.202.30.40', 2) --returns 30SELECT PARSENAME('101.202.30.40', 1) --returns 40PARSENAME is actually intended to parse the full 4-part naming structure for an object reference (server.database.owner.object), but it works identically on IP addresses!I don't know if you want to do this, but why not consolidate the GoodIPs and BadIPs tables, and add a "Status" column that indicates good or bad:CREATE TABLE AllIPs (IP varchar(15) NOT NULL PRIMARY KEY, DNS varchar(255) NOT NULL, UsrAgnt varchar(255) NULL, Name varchar(50), Status char(1) NOT NULL DEFAULT ('G') )I took the liberty of removing the ID column and making IP address as primary key, I imagine you wouldn't want duplicate IP addresses (you can always put back the ID column, but you really don't need it) The nice thing about this is that it'll avoid the possibility of having the same IP address appear as both good and bad. You can make the Status column any data type, I set it up with a single character, and you can use "G" for good and "B" for bad, or any other encoding scheme you want.The GoodUA can stay as is; you could even drop the ID column from that and make UA the primary key (can you tell I'm not big on identity columns?  )Edited by - robvolk on 04/04/2002 17:14:07 |
|
|
|
Johnhamman
Starting Member
37 Posts |
Posted - 2002-04-04 : 19:51:39
|
| Im changing the database to same as you had it, except i do need ID in there because there are (beleive it or not) duplicate IP addresses. and some are good and some are bad. Since this script is for protection of our code from certian viewser i have to check both the useragent and ip address. for example if ip address :204.152.191.* might be a bot checking the site or i might be babelfish translater. If its the translater i want it to see certian pages and not others. the reason for the class c ip is cause there might be a new bot and I dont want to take a chance that its a spoof.john |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-05 : 08:55:55
|
GREAT post Rob, didn't even think to go down the ParseName() route.  setBasedIsTheTruepath<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 09:45:36
|
 I love PARSENAME! (can a man love a function? I don't know....maybe we shouldn't explore that further)It also works well for parsing phone numbers, addresses (check this link out), brightens whites, and handles your most delicate chopping! |
|
|
|
|
|
|
|
|
Fastest Comparison for 3 tables in Stored Proc