Sunday, February 22, 2009

Get MAC Addresses Via SQL

The Media Access Control (MAC) address of a computer's network adapter is sometimes used as a unique identifier for that computer. This article presents code that lets you query the MAC address(es) via SQL using SQL Anywhere on Windows.

The MAC address is not perfect as a unique identifier; here are the main reasons:

  • the MAC address changes when you change network adapters,

  • a computer can have more than one network adapter, and thus more than one MAC address (up to 16), and

  • it is possible to change (hack) the MAC address.
But, as Engineers often say, for all practical purposes the MAC address works as a unique id.

The code shown here has been tested using SQL Anywhere 11 running on XP SP3 and Vista 64-bit; the 32-bit version of SQL Anywhere was used on Vista 64 simply because the code shown here hasn't been compiled for 64 bits yet.

Here's the SQL code your application needs to retrieve all the MAC addresses in use by the computer that's running SQL Anywhere:
SELECT * 
FROM mac_address()
ORDER BY mac_address;
Here's the output from tests on three different computers; in each case, the result set from the SELECT is shown, followed by the output from the Windows ipconfig /all command to show that the result sets match "Physical Address" values:
-------------------------------------------------------------
Machine 1 - Windows XP SP3 - one network adapter

mac_address
0x001636193239

C:\>ipconfig /all
...
Ethernet adapter Local Area Connection:
...
Physical Address. . . . . . . . . : 00-16-36-19-32-39

-------------------------------------------------------------
Machine 2 - Windows XP SP3 - two network adapters

mac_address
0x0011d82bccd7
0x00179a3b5162

C:\>ipconfig /all
...
Ethernet adapter Local Area Connection:
...
Physical Address. . . . . . . . . : 00-17-9A-3B-51-62
...
Ethernet adapter Local Area Connection 2:
...
Physical Address. . . . . . . . . : 00-11-D8-2B-CC-D7

-------------------------------------------------------------
Machine 3 - Vista 64 - one network adapter

mac_address
0x001cc04d09b5

C:\>ipconfig /all
...
Ethernet adapter Local Area Connection:
...
Physical Address. . . . . . . . . : 00-1C-C0-4D-09-B5
Here is the SQL procedure called by the SELECT above; mac_address() calls a C procedure called getmac, breaks up the VARBINARY ( 96 ) return value into 6-byte substrings and returns them as a result set:
CREATE PROCEDURE mac_address()
RESULT ( mac_address VARBINARY ( 6 ) )
BEGIN

DECLARE @mac_addresses VARBINARY ( 96 );
DECLARE @return_code INTEGER;
DECLARE @diagnostic_code INTEGER;
DECLARE @diagnostic_string VARCHAR ( 255 );
DECLARE @pos INTEGER;

DECLARE LOCAL TEMPORARY TABLE mac_address (
mac_address VARBINARY ( 6 ) NOT NULL PRIMARY KEY )
NOT TRANSACTIONAL;

CALL getmac (
@mac_addresses,
@return_code,
@diagnostic_code,
@diagnostic_string );

IF @return_code = 0 THEN

SET @pos = 1;
WHILE @pos < LENGTH ( @mac_addresses ) LOOP
INSERT mac_address VALUES (
SUBSTR ( @mac_addresses, @pos, 6 ) );
SET @pos = @pos + 6;
END LOOP;

ELSE
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP,
' getmac error: @return_code = ', @return_code,
', @diagnostic_code = ', @diagnostic_code,
', @diagnostic_string = "', @diagnostic_string,
'"' ) TO CONSOLE DEBUG ONLY;
END IF;

SELECT mac_address
FROM mac_address;

END;
Here is the SQL declaration of the getmac EXTERNAL procedure, pointing to the getmac entry point in the getmac.dll (IMO there's no good reason for using different names for something that's really the same, it just makes life unnecessarily complex):
CREATE PROCEDURE getmac (
OUT mac_addresses VARCHAR ( 96 ),
OUT return_code INTEGER, -- OK if = 0
OUT diagnostic_code INTEGER,
OUT diagnostic_string VARCHAR ( 255 ) )
EXTERNAL NAME 'getmac@C:\\temp\\getmac.dll';
Here is the getmac.cpp code, followed by the getmac.def file; the code is based on "Method Three: Use GetAdaptersInfo" from Three ways to get your MAC address by Khalid Shaikh.
// Required files:
// getmac.cpp -- provided
// getmac.def -- provided
// getmac.sln
// getmac.vcproj
//
// To open the DLL project in Visual C++ 2005:
// Doubleclick on getmac.vcproj
//
// To specify the location of SQL Anywhere *.h
// include files in Visual C++ 2005:
// Solution Explorer
// - select getmac
// - right mouse
// - Properties
// - expand Configuration Properties
// - select C/C++
// - select General
// - see Additional Include Directories
// For SQL Anywhere 10:
// "C:\Program Files\SQL Anywhere 10\h"
// For SQL Anywhere 11:
// "C:\Program Files\SQL Anywhere 11\SDK\Include"
//
// To specify which DLL functions can be named
// in CREATE PROCEDURE ... EXTERNAL statements:
// Edit the getmac.def file.
//
// To build the DLL:
// Build menu item
// - Build getmac, or Rebuild getmac
//
// To find and copy the resulting DLL...
// C:\...\getmac\Release\getmac.dll
//
// Attribution for SQL Anywhere C API sample code...
// *******************************************************
// Copyright 1994-2007 iAnywhere Solutions, Inc.
// All rights reserved.
// This sample code is provided AS IS, without warranty
// or liability of any kind.
// You may use, reproduce, modify and distribute this
// sample code without limitation, on the condition that
// you retain the foregoing copyright notice and
// disclaimer as to the original iAnywhere code.
// *******************************************************
//
// Attribution and comment header from
// www.codeguru.com/cpp/i-n/network/networkinformation
// /article.php/c5451
// *******************************************************
// GetMACAdapters.cpp : Defines the entry point for the
// console application.
// Author: Khalid Shaikh [Shake@ShakeNet.com]
// Date: April 5th, 2002
// This program fetches the MAC address of the localhost
// by fetching the information through GetAdapatersInfo.
// It does not rely on the NETBIOS protocol and the
// ethernet adapter need not be connect to a network.
// Supported in Windows NT/2000/XP
// Supported in Windows 95/98/Me
// Supports multiple NIC cards on a PC.
// *******************************************************

#include
#include
#include
#include
#include

#if defined( _MSC_VER )
#include
#endif

#include "extfnapi.h"
#include "Shlobj.h"
#include
#pragma comment(lib, "iphlpapi.lib")

#if !defined( _unused )
#define _unused( i ) ( (i) = (i) )
#endif

#if !defined( TRUE )
#define TRUE 1
#endif

#if !defined( FALSE )
#define FALSE 0
#endif

#if defined( WIN32 )
#define _UINT32_ENTRY unsigned int FAR __stdcall
#define _VOID_ENTRY void FAR __stdcall
#else
#define _UINT32_ENTRY unsigned int
#define _VOID_ENTRY void
#endif

#define int64 __int64
#define uint64 unsigned __int64

#if defined( WIN32 )
int __stdcall LibMain(
HANDLE inst,
ULONG reasoncalled,
LPVOID reserved )
/*********************/
{
_unused( inst );
_unused( reasoncalled );
_unused( reserved );
return( 1 );
}
#endif

__declspec (dllexport ) _UINT32_ENTRY
extfn_use_new_api ( void )
{
return ( EXTFN_API_VERSION );
}

//------------------------------------------
// getmac
//------------------------------------------

__declspec(dllexport) _VOID_ENTRY getmac (
an_extfn_api *api, void *arg_handle ) {

an_extfn_value api_mac_addresses;
an_extfn_value api_return_code;
an_extfn_value api_diagnostic_code;
an_extfn_value api_diagnostic_string;
char * mac_addresses;
DWORD mac_addresses_size;
DWORD return_code;
DWORD diagnostic_code;
char * diagnostic_string;
IP_ADAPTER_INFO AdapterInfo [ 16 ]; // up to 16 NICs
DWORD dwBufLen;
PIP_ADAPTER_INFO pAdapterInfo;

mac_addresses = ( char * ) malloc ( 96 );
strcpy_s ( mac_addresses, 96, "" );
mac_addresses_size = 0;

return_code = 0;
diagnostic_code = 0;
diagnostic_string = ( char * ) malloc ( 255 );
strcpy_s ( diagnostic_string, 255, "" );

dwBufLen = sizeof ( AdapterInfo );

return_code = GetAdaptersInfo (
AdapterInfo, // [out] buffer to receive data
&dwBufLen); // [in] size of receive data buffer

if ( return_code != ERROR_SUCCESS ) {
strcpy_s ( mac_addresses, 96, "" );
mac_addresses_size = 0;
diagnostic_code = 1;
strcpy_s ( diagnostic_string, 255,
"GetAdaptersInfo call failed" );
}

if ( return_code == ERROR_SUCCESS ) {

pAdapterInfo = AdapterInfo;

return_code = memcpy_s (
&mac_addresses [ 0 ],
6,
pAdapterInfo -> Address,
6 );

if ( return_code == ERROR_SUCCESS ) {
mac_addresses_size = 6;
pAdapterInfo = pAdapterInfo -> Next; // linked list
}
else {
strcpy_s ( mac_addresses, 96, "" );
mac_addresses_size = 0;
diagnostic_code = 2;
strcpy_s ( diagnostic_string, 255,
"memcpy_s call failed" );
}

while ( ( return_code == ERROR_SUCCESS )
&& ( pAdapterInfo )
&& ( mac_addresses_size < 96 ) ) {

return_code = memcpy_s (
&mac_addresses [ mac_addresses_size ],
6,
pAdapterInfo -> Address,
6 );

if ( return_code == ERROR_SUCCESS ) {
mac_addresses_size = mac_addresses_size + 6;
pAdapterInfo = pAdapterInfo -> Next; // in list
}
else {
strcpy_s ( mac_addresses, 96, "" );
mac_addresses_size = 0;
diagnostic_code = 3;
strcpy_s ( diagnostic_string, 255,
"memcpy_s call failed" );
}

} // while

} // if ( return_code == ERROR_SUCCESS )

api_mac_addresses.type = DT_VARCHAR;
api_mac_addresses.data = mac_addresses;
api_mac_addresses.piece_len = mac_addresses_size;
api_mac_addresses.len.total_len = mac_addresses_size;
api -> set_value (
arg_handle, 1, &api_mac_addresses, 0 );

api_return_code.type = DT_INT;
api_return_code.data = &return_code;
api -> set_value (
arg_handle, 2, &api_return_code, FALSE );

api_diagnostic_code.type = DT_INT;
api_diagnostic_code.data = &diagnostic_code;
api -> set_value (
arg_handle, 3, &api_diagnostic_code, FALSE );

api_diagnostic_string.type = DT_VARCHAR;
api_diagnostic_string.data = diagnostic_string;
api_diagnostic_string.piece_len
= ( a_sql_uint32 )( strlen ( diagnostic_string ) );
api_diagnostic_string.len.total_len
= ( a_sql_uint32 )( strlen ( diagnostic_string ) );
api -> set_value (
arg_handle, 4, &api_diagnostic_string, 0 );

free ( mac_addresses );
free ( diagnostic_string );

} // getmac
Here is the getmac.def file:
EXPORTS extfn_use_new_api
EXPORTS getmac

3 comments:

Jason said...

I think this would also work on any machine that actually has ipconfig.exe:

create variable ipinfo long varchar;
select xp_cmdshell( 'ipconfig /all > c:\\temp\\ip.dat' );
select substring( ipinfo, locate( ipinfo, 'physical address', 1 ) + 36, 17) as mac_address from dummy;

If you had multiple network cards, you would have to use a loop, but on Windows this should work.
Of course, you could also use a fancy regular expression to process the ipconfig output...

Breck Carter said...

Works great! if...

- xp_cmdshell hasn't been dropped from the database for security reasons

- the folder c:\temp exists

- you add the line set ipinfo = xp_read_file ( 'c:\\temp\\ip.dat' );

Everybody stand back! Biff knows regular expressions!

Jason said...

Thanks for the correction.
I never claimed *I* knew regular expressions, only that *you* could use one.