iPad and DHCP

I started wondering about the iPad and DHCP after noticing in my router's log files that the iPad was pretty chatty; re-associating with the AP really often even when sleeping. Here is about 40 minutes' worth of router logs, and notice the iPad associates with the AP every minute or so (I replaced my MAC address with "iPad" here):

[INFO] Sun Apr 25 14:39:52 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:39:52 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:38:52 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:38:52 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:37:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:37:51 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:36:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:36:51 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:35:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:35:50 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:34:50 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:34:50 2010 Wireless system with MAC address "iPad" associated
... more of the same ...
[INFO] Sun Apr 25 14:03:10 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:03:10 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:03:01 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:03:01 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:02:01 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:02:01 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:01:53 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:01:53 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:00:53 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:00:53 2010 Wireless system with MAC address "iPad" associated

The really scary thing about this is the iPad was asleep, sitting on the charger for all but the last 10 minutes or so of this log. So it appears to be chatting up the network even when you're not using it.

I ran a network sniffer from another machine to capture broadcast traffic on the wireless LAN which backed up what I saw in the router log; I have a DHCP request and acknowledgment about every minute, as well as a bunch of ARP traffic. This is the sequence that was repeated every minute:

Time        Source        Destination           Protocol Info
35.434182   0.0.0.0       255.255.255.255       DHCP     DHCP Request  - Transaction ID "iPad"
35.440707   192.168.0.1   255.255.255.255       DHCP     DHCP ACK      - Transaction ID "iPad"
35.441587   "iPad"        Broadcast             ARP      Who has 169.254.255.255?  Tell 192.168.0.109
35.442794   "iPad"        Broadcast             ARP      Gratuitous ARP for 192.168.0.109 (Request)
35.533334   "iPad"        Broadcast             ARP      Gratuitous ARP for 0.0.0.0 (Request)
35.793304   "iPad"        Broadcast             ARP      Who has 169.254.255.255?  Tell 192.168.0.109
36.194540   "iPad"        Broadcast             ARP      Who has 169.254.255.255?  Tell 192.168.0.109
36.444386   "iPad"        Broadcast             ARP      Who has 192.168.0.1?  Tell 192.168.0.109
36.595654   "iPad"        Broadcast             ARP      Who has 169.254.255.255?  Tell 192.168.0.109
41.468674   "iPad"        Broadcast             ARP      Who has 192.168.0.2?  Tell 192.168.0.109

Now a normal DHCP request has 4 packets: discover, offer, request, and acknowledgment (or 'ack'). A DHCP renewal just uses the last two: the request and the ack. There's something fishy about this, though:

1. For a DHCP renewal it is common to send my request packet to the DHCP server I've been using; that is, the first packet should be to 192.168.0.1, not to the broadcast address 255.255.255.255.
   
2. One interesting item in the ARP requests is that the iPad looks for address 169.254.255.255, which is reserved as an "autoconfiguration" address for devices that cannot be configured using DHCP. Why would it be looking for that address if it had processed the DHCP acknowledgment?
   
3. I think the iPad must have seen the DHCP acknowledgment because it looks for the DNS servers, 192.168.0.1 and 192.168.0.2, listed in the DHCP ack. How else would it be interested in those specific IP addresses?
   
4. This could be a WiFi association problem instead of strictly DHCP-related; if it were aware that it was still on my network it wouldn't need to send a DHCP renewal for 12 hours, since my lease times are set up for 24. Why is it re-associating every minute and sending a fresh DHCP request? Is it because it doesn't know it has a lease, or because it doesn't know what network it is attached to? I think it could be either.
   
5. If the machine associates to a new wireless access point (or doesn't realize it is on the same network), there's a good reason for it to send out a DHCP request packet (though honestly I think it should be starting over at 'discover' if it doesn't know where it is) - it could be that sending the request packet to the broadcast address was an attempt to workaround some problem with renewals on the same network versus requests on a new network.
   

This is all a mystery to me, but I will say I think there's something odd going on with the network interface on the iPad. The next step is to setup a wireless access point with a sniffer onboard to see if I can see not only the broadcast traffic, but any direct communication between the router and the iPad as well. I welcome comments from people who may know more than I do about DHCP, the iPad, or wireless networking.

Reinstalling Windows on a netbook

A friend of mine and I both bought HP mini netbooks when they came out last year. As it turns out, we were both ready to reload them and pass them along to other people this year. I spent some time figuring out how to do this, and wrote up instructions for my friend. They worked for him, so maybe they can also help you. Use these instructions at your own risk.

Some things to know before you get started:
1. The first thing you'll need to do is prepare an SD card with the Windows install and the contents of your HP driver disc. I did that using a lot of help and tools I found here: http://www.liliputing.com/2008/04/install-windows-xp-on-mini-note-usb.html - I used a 4GB flash card so I'd have room to copy the HP disc onto the same card, which ended up working well. You don't need a special process with the HP disc, just copy the files into a folder on the card.

2. You need the CD-Key that is on the sticker on the bottom of your laptop (check to see you haven't worn it off somehow, nice placement HP) and you'll need to activate windows once it is installed (it went smoothly for me, and I did it twice).

Here is the procedure as I explained it to my friend:

1. You might want to make sure the little tab on the SD card is set to 'Lock' - slide it down away from the business end of the card. I set it that way and I think it is safest, that should make it read-only.
2. Insert SD card. It is probably best to remove all the other flash drives, peripherals, etc. from the machine to simplify things.
3. Boot, hit F9 to choose boot device, choose the flash card (should say USB I think)
4. You'll get a standard XP boot prompt - choose the text only install (should be labeled '1.') to start (you'll switch to GUI after reboot)
5. Follow windows setup prompts:

a. Even if you already have a C:\ drive, go ahead and delete the partition on your internal disk and start again. Format it NTFS Quick.

b. If for some reason the SD card comes up as drive C:\, try to change it so your internal disk gets to be C:\ - I don't think this will happen to you since you already have windows on the machine but it happened to me when I switched from Linux back to Windows.

6. Keep watch while windows formats the disk and copies the appropriate files (approx. 10 minutes or so), so you can be ready when the system reboots.
7. On reboot, hit F9 to choose the boot device again!
8. You'll get a standard XP boot prompt - choose the GUI install option this time.
9. This should be the standard XP setup you're used to. Sadly the USB isn't really any faster than a CD, but you don't need to babysit it.

a. Type in your CD Key from the sticker on the bottom of your machine, crap, no mouse! use a combination of tab, enter, 'N' key to move on.

b. It will wait for you to type in the CD key, and your first reboot after the install will probably fail, so its ok to wander off while this runs.

c. For some reason mine took FOREVER at the "Removing any temporary files used... 1 minute remaining" part. It was a LOT more than 1 minute.

10. Once windows has installed, the system will try to reboot but you will get an error:

"Windows could not start because the following file is missing or corrupt: \system32\hal.dll. Please re-install a copy of the above file."

Hit enter and the system should try to reboot. Hit F9 and boot from the SD card again. Choose the GUI install option again. This should actually boot you into windows. I have no idea why this works, haha.
11. You should be presented a login screen with two users: Owner and UserXP. I've been using Owner and it seems to work. Click Owner to login.
12. Windows will gripe a bit about the display being low-res. Don't worry about it; the HP driver install will fix all of that.
13. Start->My Computer->C:\->show contents. You should have a boot.ini file in C:\ - you might need to go to Tools->Folder options and change some of the settings to see it, but probably not. double-click boot.ini to edit the file. You shouldn't need to change much. I made mine look like this:

    [Boot Loader]
    timeout=1
    Default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
    [Operating Systems]
    multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Home Edition" /noexecute=optin /fastdetect

14. Remove the UserXP user account from the control panel. Start, Control Panel, User accounts, click on UserXP, click 'Delete this account', don't bother saving files or anything.

15. Go to the SD card (mine was D:\ - the label is USB_XP_710), open the HP folder, open SWSETUP, open APPINSTL, double-click SETUP.EXE

a. Open the tree and uncheck boxes for things you don't want to install - for instance, I don't have bluetooth or a WWAN card so I unchecked these underneath "Hardware Enabling Drivers," and I didn't want instant messenger so I unchecked it under "Recommended Software Applications"

b. In particular, don't bother installing Java. It doesn't seem to work consistently. Just uncheck it and install it from the web later if you need it.

c. Click 'Install' and walk away for an hour or so. It will pop up some boxes that look like you need to do something, but you don't. Just wait the process out. You'll be prompted to reboot at the end.

16. Reboot. Don't hit F9 this time and see if the system can boot on its own. If it can, you're ready to run windows update, activate windows, etc. If it can't try booting from the SD card again and see if you can figure out what has happened. The boot ini needs to point to the internal hard drive (usually disk 0, partition 1 but there might be a reason it is something else).


I hope this helps you. Use these instructions at your own risk.

Database Tracing in Oracle

You can trace statements in Oracle using a package called "dbms_monitor" - it allows you to see statements that are executed along with some indication of how efficient they are (sort of like using an explain plan, but after the fact). Why would you want to do this? Well, if you have an application that is misbehaving or if you want to know more about how your statements are running live (as opposed to using the explain plan) this can help. Also, if you have a closed package and want to know more about what it is doing in the database this might help. You'll need to be a DBA or be friendly with your DBA to do this, as it requires some privileges and logs are written to the database servers' filesystem.

1. Get access to the dbms_monitor package. Your DBA can grant you rights. I do not know the details on this step, I'm afraid.
   
   
2. Do a select to find out what sessions are active:
   

   
     SELECT sid, serial#, client_identifier, service_name, action, module, username
     FROM V$SESSION 
   

   
   I'd actually use a "WHERE username='my_username'" clause of some kind here to limit what I get, but YMMV. You'll end up with rows that look something like this:
   

   
   SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION      MODULE     USERNAME
   266 24923                     My_Instance  SQL Window  SQL Devel  my_username
   

   
   
3. Enable logging:
   
   
   
   1. To log by user session, you need to make a note of the SID and SERIAL# columns, and run this to begin tracing:
      

      
        begin
        dbms_monitor.SESSION_TRACE_ENABLE(266, 24923, TRUE, TRUE);
        end;
      

      
      
   2. To log by service and module, you can use those columns:
      

      
        begin
        dbms_monitor.serv_mod_act_trace_enable('My_Instance', 'SQL Devel', 
          DBMS_MONITOR.ALL_ACTIONS, TRUE, TRUE, NULL);
        end;
      

      
      An upside to doing tracing by service is you can verify it is running:
      

      
      select * from DBA_ENABLED_TRACES
      
      TRACE_TYPE      PRIMARY_ID  QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME
      SERVICE_MODULE  My_Instance SQL Devel                   TRUE  TRUE 
      

      
      
   
   
4. At some point you will have had enough logging, and you'll want to turn it off.
   
   
   
   1. If you are tracing by user/session:
      

      
        begin
        dbms_monitor.SESSION_TRACE_DISABLE(266, 24923);
        end;
      

      
      
   2. If you are tracing by service/module:
      

      
        begin
        dbms_monitor.serv_mod_act_trace_disable ('My_Instance', 'SQL Devel');
        end;
      

      
      note: with service level tracing you can select * from DBA_ENABLED_TRACES to verify tracing has been turned off, just as you did to determine it was on.
      
   
   
5. So you've traced some information. Where is it? In a file. Where is the file?
   

   
   select value from v$parameter where name = 'user_dump_dest'
   
   VALUE
   /usr/local/oracle/10204/admin/My_Instance/udump
   

   
   
6. Once you have the file (ask your DBA for the contents of that folder, or better yet the files from today, or this hour or something) you might wonder what is inside. Well, it is only semi-readable at this point. You need to use a utility called TKPROF to reformat the trace into something you can really read. I found I already had a copy of TKPROF in my oracle bin directory:
   

   
   "C:\oracle\product\10.2.0\client_1\BIN\tkprof.exe" c:\my_instance_ora_23486.trc c:\my_oracle.log
   

   
   At this point, c:\my_oracle.log should contain a list of SQL statements and performance data for each statement for as long as you had tracing on.
   
   
7. For more information, check out Oracle's site:
   http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm
   

tracing in ASP.NET

Just a handy code snippet to add to your code if you've enabled tracing in your web.config:

If tracing is enabled, you may view trace information using <a href=\"http://" + this.Context.Request.Url.Authority + this.Context.Request.ApplicationPath + "/Trace.axd\">Trace.axd</a>

Encrypting connection strings in .NET

There is a lot of documentation on the web about this (my favorites are at the bottom of this post) but I really wanted a summary of how to encrypt strings using my own key that could be moved from server to server, and how to access those strings from a web app / code-behind. So this is what I came up with. I'm posting it so I can return to it later as I learn more; take it with a grain of salt.

Generating an exportable / movable RSA key and encrypting using your custom key

---

1. Generate an RSA Key container
   
aspnet_regiis -pc "SampleKeys" –exp

   
   
2. Tie the RSA key container to your web.config (<configProtectedData> is a top-level key, like connectionStrings or appSettings) - note that this should be identical to the config for RsaProtectedConfigurationProvider in your machine.config, with a different value for the defaultProvider, name and keyContainerName attributes.
   

   
   <configProtectedData defaultProvider="SampleProvider">
      <providers>
        <add name="SampleProvider" type="System.Configuration.RsaProtectedConfigurationProvider,System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" description="Uses RsaCryptoServiceProvider to encrypt and decrypt" keyContainerName="SampleKeys" cspProviderName="" useMachineContainer="true" useOAEP="false"/>
      </providers>
   </configProtectedData>
   

   
   
   
3. Grant access to the key container to the user your process runs as
   
aspnet_regiis -pa "SampleKeys" "ASPNET"

   
   
   
   1. This sets up permissions for the "default" RsaProtectedConfigurationProvider. I had to set this up for the ASPNET user along with permissions for my own container above.
      
aspnet_regiis -pa "NetFrameworkConfigurationKey" "ASPNET"

      
   2. If the user your process is running as doesn't have permission to use the provider, you'll get an error "Failed to decrypt using provider 'RsaProtectedConfigurationProvider'."
      
      
   3. I recently ran into a server that didn't have the "NetFrameworkConfigurationKey" - I think this is part of the normal .NET Framework setup so I don't know why it might have been missing. Generating the key fixed the problem:
      
aspnet_regiis -pc "NetFrameworkConfigurationKey"

      
   
   
   


4. Encrypting strings - if you're not using the default site you'll need the site identifier (shown in IIS manager window under "Web Sites"?)
   
aspnet_regiis -pe "connectionStrings" -site 1 -app "/SampleApplication" -prov "SampleProvider"

   
   
5. Other Useful Commands
   
   
   
   1. Decrypting strings (in case you need to change something)
      
aspnet_regiis -pd "connectionStrings" -app "/SampleApplication"

      
      
   2. Export the public and private keys for import on another machine
      
aspnet_regiis -px "SampleKeys" keys.xml -pri

      
      
   3. Importing the keys
      
aspnet_regiis -pi "SampleKeys" keys.xml

      
      
   4. Removing access to a no longer needed key
      
aspnet_regiis -pr "SampleKeys" "ASPNET"

      
      
   5. Deleting a no longer needed key container
      
aspnet_regiis -pz "SampleKeys"

      
   
   

Using encrypted strings in code

---

Most of the canned demo code on the internet shows you opening, testing for the strings to be encrypted, decrypting them, then saving the config file back. I don't think I want to save the unencrypted strings, as this would seem to defeat the purpose of encrypting them in the first place. I may be misunderstanding the code samples, but this is what I came up with and it makes more sense to me.

       static string GetProtectedConnectionString(string name)
       {
           // Open the Web.config file.
           Configuration config = WebConfigurationManager.
               OpenWebConfiguration("~");

           // Get the connectionStrings section.
           ConnectionStringsSection section =
               config.GetSection("connectionStrings")
               as ConnectionStringsSection;

           // Toggle encryption.
           if (section.SectionInformation.IsProtected)
           {
               section.SectionInformation.UnprotectSection();
               ConnectionStringSettings s = section.ConnectionStrings[name];
               return s.ConnectionString;
           }
           else
           {
               throw new ConfigurationException("Warning: connection strings are not encrypted");
           }           
       }

This code probably needs a bit more cleanup. The point is, you don't save the unprotected section, but you decrypt the section long enough to get the string from the config file that you were looking for. Note that you could easily modify this to just return the connection string whether or not it was encrypted but I decided I'd rather be notified if the config ends up unprotected for some reason. Which brings up a valuable point: when you re-publish your site, you will need to re-encrypt your strings! Add this as a post-build step!


Useful links

---

Securing Connection Strings
http://msdn.microsoft.com/en-us/library/89211k9b(VS.80).aspx

Encrypting Configuration Information Using Protected Configuration
http://msdn.microsoft.com/en-us/library/53tyfkaw(VS.80).aspx

Importing and Exporting Protected Configuration RSA Key Containers
http://msdn.microsoft.com/en-us/library/yxw286t2(VS.80).aspx

Walkthrough of encrypting connection strings and accessing them from ASPx
http://msdn.microsoft.com/en-us/library/dtkwfdky(VS.80).aspx

How To: Encrypt with ASP.NET 2.0 using RSA
http://msdn.microsoft.com/en-us/library/ms998283.aspx

munging numbers and strings in Oracle

An item of current interest to me is a compound number. Sometimes I see it in the database as one big number: 12345678901234 and at other times I see it in the database as two shorter numbers: 1234567890 and 1234. So how can I go back and forth? There are a few tricks I've learned to keep up with it:

1. Always make sure you know which data type the item you want is being stored as. Sometimes this ID is stored as a string, sometimes as a number. Figuring this out depends on the tools you use, but make sure you know what you're working with - you can usually use DESC to find out if you're just sitting at a SQL prompt. Also, I'm assuming in these examples that I can trust my data to be entered correctly (e.g. no one accidentally put a letter in somewhere), which is probably a stretch in real life but will work in my particular case.
   
   
2. When it is two numeric fields and I need one string field, I can format and concatenate the two numbers into a big string:
   CONCAT(TO_CHAR(first_number, 'FM0000000000'),TO_CHAR(second_number, 'FM0000'))
   It took some looking things up to figure out that adding the 'FM' at the beginning of the format string would keep Oracle from leaving a space at the beginning for the +/- sign, which was key to actually matching the string.
   
   
3. When it is one big string field and I need two numbers, I can split them:
   
SELECT whatever FROM you_know_what WHERE TO_NUMBER(SUBSTR(big_string_field, 1, 10)) = first_number AND TO_NUMBER(SUBSTR(big_string_field, -4)) = second_number

   Number positions start with 1 in SQL (not 0 like in code), so that first SUBSTR gets the first 10 characters of my string, and the second gets the last 4 (negative position counts backwards from the end of the string). Note that if there are non-numeric characters in the string, TO_NUMBER would fail, everyone dies, oh the embarrassment.
   

Stupid Oracle SQL Tricks, part 2

grouping dates by week:
to_char(my_date + (1-to_char(my_date,'D')),'YYYY-MM-DD') Week_Of_My_Date
You're basically subtracting the day of the week and ending up with Sunday for any day in a given week.

finding records by today's date or last month or two or three months ago:
select count(my_whatever) from my_table t where t.my_date > add_months(current_date, -2) and t.my_date <= current_date
Oracle uses sysdate for today, which is really common and great and all but I've learned that almost all database systems (really all but SQL Server) support current_date, which is more descriptive and works in more places, FTW!


the median (middle) item of a list:
select PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY my_item DESC) median_item from my_table
This is slightly more complicated than just the 'median' (meaning it may not do what you think its doing) so to understand further take a look at the Oracle manual.