I wanted to write about a really weird problem I recently had while debugging in C++ (technically, it’s all C). Unfortunately, I was doing this in kernel debugging mode, which made life a bit harder, but it would have happened the same in userland.

I had an .hpp file (we’ll call it process_internal.hpp) that was originally an internal file just to be included from a .cpp file (we’ll call it process.cpp), so it contained global variables as symbols. I ended up needing to include this process_internal.hpp file elsewhere (for testing, we’ll call it test.cpp). Because of this, the same symbol was included in multiple files, so the separate .o builds were not properly interacting. I ended up using “#ifdef”s to only include the parts I needed in the test.cpp file, and doing “extern” defines of the global variables for it. It looked something like the following:

enum { FT_Inbound, FT_Outbound };
typedef struct FilteringLayer {
	int FilterTypeNum, OriginalID;
	const char *Name;
} FilteringLayer;
const int FT_NumTypes=2;

#ifdef _PROCESS_INTERNAL
	FilteringLayer FilterTypes[FT_NumTypes]={
		{FT_Inbound,  5, "Inbound"),
		{FT_Outbound, 8, "Outbound"),
	};
#else
	extern "C" FilteringLayer *FilterTypes;
#endif

So I was accessing this variable in test.cpp and getting a really weird problem. The code looked something like this:

struct foo { int a, b; };
foo Stuff[]={...};
void FunctionBar()
{
	for(int i=0;i<FT_NumTypes;i++)
		Stuff[FilterTypes[i].OriginalID].b=1;
}

This was causing an access exception, which blue screened my debug VM. I tried running the exact same statements in the visual studio debugger, and things were working just as they were supposed to! So I decided to go to the assembly level. It looked something like this: (I included descriptions)

I noticed that line #9 was putting 0x0000000C`00000000 into RCX instead of &FilterTypes. I knew the instruction should have been an “lea” instead of a “mov” to fix this. My first thought was compiler bug, but as many programming mantras say, that is very very rarely the case. If you want to guess now what the problem is, now is the time. I’ve given you all the information (and more) to make the guess.

The answer: extern "C" FilteringLayer *FilterTypes; should have been extern "C" FilteringLayer FilterTypes[];. Oops! The debugger was getting it right because it had the extra information of the real definition of the FilterTypes variable.

1. INSERT: INSERT with ON DUPLICATE KEY UPDATE
   

   			INSERT INTO FooBar (ID, foo)
   			VALUES (1, 5), (2, 8), (3, 2)
   			ON DUPLICATE KEY UPDATE foo=VALUES(foo);
   		

2. TRANSACTION: Where you do an update for each record within a transaction (InnoDB or other DBs with transactions)
   

   			START TRANSACTION;
   			UPDATE FooBar SET foo=5 WHERE ID=1;
   			UPDATE FooBar SET foo=8 WHERE ID=2;
   			UPDATE FooBar SET foo=2 WHERE ID=3;
   			COMMIT;
   		

3. CASE: In which you a case/when for each different record within an UPDATE

   			UPDATE FooBar SET foo=CASE ID
   				WHEN 1 THEN 5
   				WHEN 2 THEN 8
   				WHEN 3 THEN 2
   			END
   			WHERE ID IN (1,2,3);
   		

I feel knowing the speeds of the 3 different methods is important.

All of the following numbers apply to InnoDB.

I just tested this, and the INSERT method was 6.7x faster for me than the TRANSACTION method. I tried on a set of both 3,000 and 30,000 rows and got the same results.

The TRANSACTION method still has to run each individually query, which takes time, though it batches the results in memory, or something, while executing. The TRANSACTION method is also pretty expensive in both replication and query logs.

Even worse, the CASE method was 41.1x slower than the INSERT method w/ 30,000 records (6.1x slower than TRANSACTION). And 75x slower in MyISAM. INSERT and CASE methods broke even at ~1,000 records. Even at 100 records, the CASE method is BARELY faster.

So in general, I feel the INSERT method is both best and easiest to use. The queries are smaller and easier to read and only take up 1 query of action. This applies to both InnoDB and MyISAM.

Using the INSERT method, there can be a problem in which NON-NULL fields with no default (in other words, required fields) are not being updated. You will get an error like “Field 'fieldname' doesn't have a default value”. The solution is to temporarily turn off STRICT_TRANS_TABLES and STRICT_ALL_TABLES in the SQL mode: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). Make sure to save the sql_mode first if you plan on reverting it.

As for other comments I’ve seen that say the auto_increment goes up using the INSERT method, I tested that too and it seems to not be the case.

<?
//Variables
$NumRows=30000;

//These 2 functions need to be filled in
function InitSQL()
{

}
function RunSQLQuery($Q)
{

}

//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
{
    $TheQueries=Array();
    $DoQuery=function($Query) use (&$TheQueries)
    {
        RunSQLQuery($Query);
        $TheQueries[]=$Query;
    };

    $TableName='Test';
    $DoQuery('DROP TABLE IF EXISTS '.$TableName);
    $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
    $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');

    if($TestNum==0)
    {
        $TestName='Transaction';
        $Start=microtime(true);
        $DoQuery('START TRANSACTION');
        for($i=1;$i<=$NumRows;$i++)
            $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
        $DoQuery('COMMIT');
    }

    if($TestNum==1)
    {
        $TestName='Insert';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
    }

    if($TestNum==2)
    {
        $TestName='Case';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
    }

    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}

I recently had a Linux client that was, for whatever odd reason, making infinite recursive HTTP calls to a single script, which was making the server process count skyrocket. I decided to use the same module as I did in my Painless migration from PHP MySQL to MySQLi post, which is to say, overriding base functions for fun and profit using the PHP runkit extension. I did this so I could gather, for debugging, logs of when and where the calls that were causing this to occur.

The below code overrides all functions listed on the line that says “List of functions to intercept” [Line 9]. It works by first renaming these built in functions to “OVERRIDE_$FuncName” [Line 12], and replacing them with a call to “GlobalRunFunc()” [Line 13], which receives the original function name and argument list. The GlobalRunFunc():

1. Checks to see if it is interested in logging the call
   • In the case of this example, it will log the call if [Line 20]:
     • Line 21: curl_setopt is called with the CURLOPT_URL parameter (enum=10002)
     • Line 22: curl_init is called with a first parameter, which would be a URL
     • Line 23: file_get_contents or fopen is called and is not an absolute path
       (Wordpress calls everything absolutely. Normally I would have only checked for http[s] calls).
   • If it does want to log the call, it stores it in a global array (which holds all the calls we will want to log).
     The logged data includes [Line 25]:
     • The function name
     • The function parameters
     • 2 functions back of backtrace (which can often get quite large when stored in the log file)
2. It then calls the original function, with parameters intact, and passes through the return [Line 27].

The “GlobalShutdown()” [Line 30] is then called when the script is closing [Line 38] and saves all the logs, if any exist, to “$GlobalLogDir/$DATETIME.srl”.

I have it using “serialize()” to encode the log data [Line 25], as opposed to “json_encode()” or “print_r()” calls, as the latter were getting too large for the logs. You may want to have it use one of these other encoding functions for easier log perusal, if running out of space is not a concern.

<?
//The log data to save is stored here
global $GlobalLogArr, $GlobalLogDir;
$GlobalLogArr=Array();
$GlobalLogDir='./LOG_DIRECTORY_NAME';

//Override the functions here to instead have them call to GlobalRunFunc, which will in turn call the original functions
foreach(Array(
        'fopen', 'file_get_contents', 'curl_init', 'curl_setopt', //List of functions to intercept
) as $FuncName)
{
        runkit_function_rename($FuncName, "OVERRIDE_$FuncName");
        runkit_function_add($FuncName, '', "return GlobalRunFunc('$FuncName', func_get_args());");
}

//This optionally 
function GlobalRunFunc($FuncName, $Args)
{
        global $GlobalLogArr;
        if(
                ($FuncName=='curl_setopt' && $Args[1]==10002) || //CURLOPT enumeration can be found at https://curl.haxx.se/mail/archive-2004-07/0100.html
                ($FuncName=='curl_init' && isset($Args[0])) ||
                (($FuncName=='file_get_contents' || $FuncName=='fopen') && $Args[0][0]!='/')
        )
                $GlobalLogArr[]=serialize(Array('FuncName'=>$FuncName, 'Args'=>$Args, 'Trace'=>array_slice(debug_backtrace(), 1, 2)));

        return call_user_func_array("OVERRIDE_$FuncName", $Args);
}

function GlobalShutdown()
{
        global $GlobalLogArr, $GlobalLogDir;
        $Time=microtime(true);
        if(count($GlobalLogArr))
                file_put_contents($GlobalLogDir.date('Y-m-d_H:i:s.'.substr($Time-floor($Time), 2, 3), floor($Time)).'.srl', implode("\n", $GlobalLogArr));

}
register_shutdown_function('GlobalShutdown');
?>

I wrote the code at the end of this post to test the different forms of string concatenation and they really are all almost exactly equal in both memory and time footprints.

The two primary methods I used are concatenating strings onto each other, and filling an array with strings and then imploding them. I did 500 string additions with a 1MB string in PHP 5.6 (so the result is a 500MB string). At every iteration of the test, all memory and time footprints were very very close (at ~$IterationNumber*1MB). The runtime of both tests was 50.398 seconds and 50.843 seconds consecutively which are most likely within acceptable margins of error.

Garbage collection of strings that are no longer referenced seems to be pretty immediate, even without ever leaving the scope. Since the strings are mutable, no extra memory is really required after the fact.

HOWEVER, The following tests showed that there is a different in peak memory usage WHILE the strings are being concatenated.

$OneMB=str_repeat('x', 1024*1024);
$Final=$OneMB.$OneMB.$OneMB.$OneMB.$OneMB;
print memory_get_peak_usage();

$OneMB=str_repeat('x', 1024*1024);
$Final=implode('', Array($OneMB, $OneMB, $OneMB, $OneMB, $OneMB));
print memory_get_peak_usage();

So there is in fact a difference that could be significant in very very large string concatenations memory-wise (I have run into such examples when creating very large data sets or SQL queries).

But even this fact is disputable depending upon the data. For example, concatenating 1 character onto a string to get 50 million bytes (so 50 million iterations) took a maximum amount of 50,322,512 bytes (~48MB) in 5.97 seconds. While doing the array method ended up using 7,337,107,176 bytes (~6.8GB) to create the array in 12.1 seconds, and then took an extra 4.32 seconds to combine the strings from the array.

Anywho... the below is the benchmark code I mentioned at the beginning which shows the methods are pretty much equal. It outputs a pretty HTML table.

<?
//Please note, for the recursion test to go beyond 256, xdebug.max_nesting_level needs to be raised.
//You also may need to update your memory_limit depending on the number of iterations

//Output the start memory
print 'Start: '.memory_get_usage()."B<br><br>Below test results are in MB<br>";

//Our 1MB string
global $OneMB, $NumIterations;
$OneMB=str_repeat('x', 1024*1024);
$NumIterations=500;

//Run the tests
$ConcatTest=RunTest('ConcatTest');
$ImplodeTest=RunTest('ImplodeTest');
$RecurseTest=RunTest('RecurseTest');

//Output the results in a table
OutputResults(
  Array('ConcatTest', 'ImplodeTest', 'RecurseTest'),
  Array($ConcatTest, $ImplodeTest, $RecurseTest)
);

//Start a test run by initializing the array that will hold the results and manipulating those results after the test is complete
function RunTest($TestName)
{
  $CurrentTestNums=Array();
  $TestStartMem=memory_get_usage();
  $StartTime=microtime(true);
  RunTestReal($TestName, $CurrentTestNums, $StrLen);
  $CurrentTestNums[]=memory_get_usage();

  //Subtract $TestStartMem from all other numbers
  foreach($CurrentTestNums as &$Num)
    $Num-=$TestStartMem;
  unset($Num);

  $CurrentTestNums[]=$StrLen;
  $CurrentTestNums[]=microtime(true)-$StartTime;

  return $CurrentTestNums;
}

//Initialize the test and store the memory allocated at the end of the test, with the result
function RunTestReal($TestName, &$CurrentTestNums, &$StrLen)
{
  $R=$TestName($CurrentTestNums);
  $CurrentTestNums[]=memory_get_usage();
  $StrLen=strlen($R);
}

//Concatenate 1MB string over and over onto a single string
function ConcatTest(&$CurrentTestNums)
{
  global $OneMB, $NumIterations;
  $Result='';
  for($i=0;$i<$NumIterations;$i++)
  {
    $Result.=$OneMB;
    $CurrentTestNums[]=memory_get_usage();
  }
  return $Result;
}

//Create an array of 1MB strings and then join w/ an implode
function ImplodeTest(&$CurrentTestNums)
{
  global $OneMB, $NumIterations;
  $Result=Array();
  for($i=0;$i<$NumIterations;$i++)
  {
    $Result[]=$OneMB;
    $CurrentTestNums[]=memory_get_usage();
  }
  return implode('', $Result);
}

//Recursively add strings onto each other
function RecurseTest(&$CurrentTestNums, $TestNum=0)
{
  Global $OneMB, $NumIterations;
  if($TestNum==$NumIterations)
    return '';

  $NewStr=RecurseTest($CurrentTestNums, $TestNum+1).$OneMB;
  $CurrentTestNums[]=memory_get_usage();
  return $NewStr;
}

//Output the results in a table
function OutputResults($TestNames, $TestResults)
{
  global $NumIterations;
  print '<table border=1 cellspacing=0 cellpadding=2><tr><th>Test Name</th><th>'.implode('</th><th>', $TestNames).'</th></tr>';
  $FinalNames=Array('Final Result', 'Clean');
  for($i=0;$i<$NumIterations+2;$i++)
  {
    $TestName=($i<$NumIterations ? $i : $FinalNames[$i-$NumIterations]);
    print "<tr><th>$TestName</th>";
    foreach($TestResults as $TR)
      printf('<td>%07.4f</td>', $TR[$i]/1024/1024);
    print '</tr>';
  }

  //Other result numbers
  print '<tr><th>Final String Size</th>';
  foreach($TestResults as $TR)
    printf('<td>%d</td>', $TR[$NumIterations+2]);
  print '</tr><tr><th>Runtime</th>';
    foreach($TestResults as $TR)
      printf('<td>%s</td>', $TR[$NumIterations+3]);
  print '</tr></table>';
}
?>

function DeepObjectCompare(O1, O2)
{
	try {
		DOC_Val(O1, O2, ['O1->O2', O1, O2]);
		return DOC_Val(O2, O1, ['O2->O1', O1, O2]);
	} catch(e) {
		console.log(e.Chain);
		throw(e);
	}
}
function DOC_Error(Reason, Chain, Val1, Val2)
{
	this.Reason=Reason;
	this.Chain=Chain;
	this.Val1=Val1;
	this.Val2=Val2;
}

function DOC_Val(Val1, Val2, Chain)
{
	function DoThrow(Reason, NewChain) { throw(new DOC_Error(Reason, NewChain!==undefined ? NewChain : Chain, Val1, Val2)); }

	if(typeof(Val1)!==typeof(Val2))
		return DoThrow('Type Mismatch');
	if(Val1===null || Val1===undefined)
		return Val1!==Val2 ? DoThrow('Null/undefined mismatch') : true;
	if(Val1.constructor!==Val2.constructor)
		return DoThrow('Constructor mismatch');
	switch(typeof(Val1))
	{
		case 'object':
			for(var m in Val1)
			{
				if(!Val1.hasOwnProperty(m))
					continue;
				var CurChain=Chain.concat([m]);
				if(!Val2.hasOwnProperty(m))
					return DoThrow('Val2 missing property', CurChain);
				DOC_Val(Val1[m], Val2[m], CurChain);
			}
			return true;
		case 'number':
			if(Number.isNaN(Val1))
				return !Number.isNaN(Val2) ? DoThrow('NaN mismatch') : true;
		case 'string':
		case 'boolean':
			return Val1!==Val2 ? DoThrow('Value mismatch') : true;
		case 'function':
			if(Val1.prototype!==Val2.prototype)
				return DoThrow('Prototype mismatch');
			if(Val1!==Val2)
				return DoThrow('Function mismatch');
			return true;
		default:
			return DoThrow('Val1 is unknown type');
	}
}

Following is some C++ source code for a Windows kernel-driver service loader. It could be used to load other service types too by changing the dwServiceType flag on the CreateService call. I threw this together for another project I am currently working on. It is also used in the following post (posting soon).

• It is a command line utility which takes 3 arguments:
  1. The service name. Hereby referred to as SERVICE_NAME
  2. The service display name. Hereby referred to as DISPLAY_NAME
  3. The driver path (to the .sys file). Hereby referred to as DRIVER_PATH
• This program (most likely) requires administrative access. There are also some caveats regarding driver code signing requirements that are thoroughly explored elsewhere.
• It first checks to see if a service already exists with the given SERVICE_NAME. If it does:
  1. If the DISPLAY_NAME matches, the service is kept as is.
  2. If the DISPLAY_NAME does not match, the user is prompted on if they want to delete the current service. If they do not, the program exits.
• If the service needs to be created (it did not already exist or was deleted), it creates the service with the given SERVICE_NAME, DISPLAY_NAME, and DRIVER_PATH. If the service is not created during this run, the DRIVER_PATH is ignored.
  Note: The DRIVER_PATH must be to a direct local file system file. I have found that network links and symbolic links do not work.
• The service is started up:
  • If it is already running, the user is prompted on if they want to stop the currently running service. If they say no, the program exits.
• The program then waits for a final user input on if they want to close the service before exiting the program.
• If there was an error, the program reports the error, otherwise, it reports “Success”.
• The program pauses at the end until the user presses any key to exit.
• The program returns 0 on success, and 1 if an error occurred.

//Compiler flags
#define WIN32_LEAN_AND_MEAN  //Include minimum amount of windows stuff
#ifndef _UNICODE //Everything in this script is unicode
	#define _UNICODE
#endif

#include <windows.h>
#include <stdio.h>
#include <conio.h>
#include <memory>

//Smart pointers
typedef std::unique_ptr<WCHAR, void(*)(WCHAR*)> SmartWinAlloc;
typedef std::unique_ptr<SC_HANDLE, void(*)(SC_HANDLE*)> SmartCloseService;
void Delete_SmartWinAlloc(WCHAR *p) { if(p) LocalFree(p); }
void Delete_SmartCloseService(SC_HANDLE *h) { if(h && *h) CloseServiceHandle(*h); }

//Function declarations
WCHAR* InitDriver(int argc, WCHAR *argv[]);
WCHAR* FormatError(WCHAR* Format, ...);
SmartWinAlloc GetLastErrorStr();
BOOLEAN AskQuestion(WCHAR* Question); //Returns if user answered yes

int wmain(int argc, WCHAR *argv[])
{
	//Run the init routine
	WCHAR* Ret=InitDriver(argc, argv);

	//If there is an error, report it, or otherwise, report success
	wprintf(L"%s\n", Ret ? Ret : L"Success");
	wprintf(L"%s\n", L"Press any key to exit");
	_getch();

	//Return if successful
	return (Ret ? 1 : 0);
}

WCHAR* InitDriver(int argc, WCHAR *argv[])
{
	//Confirm arguments
	if(argc<4)
		return FormatError(L"%s", L"3 arguments are required: Service Name, Display Name, Driver Path");
	const WCHAR* Param_ServiceName=argv[1];
	const WCHAR* Param_DisplayName=argv[2];
	const WCHAR* Param_DriverPath =argv[3];

	//Open the service manager
	wprintf(L"%s\n", L"Opening the service manager");
	SC_HANDLE HSCManager=OpenSCManager(nullptr, nullptr, SC_MANAGER_CREATE_SERVICE);
	if(!HSCManager)
		return FormatError(L"%s: %s", L"Error opening service manager", GetLastErrorStr());
	SmartCloseService FreeHSCManager(&HSCManager, Delete_SmartCloseService);

	//Check if the service already exists
	wprintf(L"%s\n", L"Checking previously existing service state");
	BOOL ServiceExists=false;
	{
		//Get the service name
		const DWORD NameBufferSize=255;
		WCHAR NameBuffer[NameBufferSize];
		WCHAR *NamePointer=NameBuffer;
		DWORD NamePointerSize=NameBufferSize;
		std::unique_ptr<WCHAR> Buf(nullptr); //May be swapped with a real pointer later
		for(INT_PTR i=0;i<2;i++)
		{
			//If we found the service, exit the lookup here
			if(GetServiceDisplayName(HSCManager, Param_ServiceName, NamePointer, &NamePointerSize))
			{
				ServiceExists=true;
				break;
			}

			//If the service does not exist, we can exit the lookup here
			if(GetLastError()==ERROR_SERVICE_DOES_NOT_EXIST)
				break;

			//If error is not insufficient buffer size, return the error
			if(GetLastError()!=ERROR_INSUFFICIENT_BUFFER)
				return FormatError(L"%s: %s", L"Could not query service information", GetLastErrorStr());

			//If second pass, error out
			if(i==1)
				return FormatError(L"%s: %s", L"Could not query service information", L"Second buffer pass failed");

			//Create a buffer of appropriate size (and make sure it will later be released)
			NamePointer=new WCHAR[++NamePointerSize];
			std::unique_ptr<WCHAR> Buf2(NamePointer);
			Buf.swap(Buf2);
		}

		//If the service already exists, confirm the service name matches, and if not, ask if user wants to delete the current service
		if(ServiceExists)
		{
			wprintf(L"%s\n", L"The service already exists");
			if(wcsncmp(NamePointer, Param_DisplayName, NamePointerSize+1))
			{
				//If the server names do not match, ask the user what to do
				wprintf(L"%s:\nCurrent: %s\nRequested: %s\n", L"The service names do not match", NamePointer, Param_DisplayName);

				//Make the request
				if(!AskQuestion(L"Would you like to replace the service? (y/n)")) //If user does not wish to replace the service
					return FormatError(L"%s", L"Cannot continue if service names do not match");

				//Delete the service
				wprintf(L"%s\n", L"Deleting the old service");
				ServiceExists=false;
				SC_HANDLE TheService=OpenService(HSCManager, Param_ServiceName, DELETE);
				if(!TheService)
					return FormatError(L"%s: %s", L"Could not open the service to delete it", GetLastErrorStr());
				SmartCloseService CloseTheService(&TheService, Delete_SmartCloseService); //Close the service handle
				if(!DeleteService(TheService))
					return FormatError(L"%s: %s", L"Could not delete the service", GetLastErrorStr());
				wprintf(L"%s\n", L"The service has been deleted");
			}
		}
	}

	//Create the service
	SC_HANDLE TheService;
	if(!ServiceExists)
	{
		//Confirm the driver path exists
		wprintf(L"%s\n", L"Checking the driver file");
		DWORD FileAttrs=GetFileAttributes(Param_DriverPath);
		if(FileAttrs==INVALID_FILE_ATTRIBUTES)
			return FormatError(L"%s: %s", L"Given path is invalid", GetLastErrorStr());
		if(FileAttrs&FILE_ATTRIBUTE_DIRECTORY)
			return FormatError(L"%s: %s", L"Given path is invalid", L"Path is a folder");

		//Create the service
		wprintf(L"%s\n", L"Creating the service");
		TheService=CreateService(
			HSCManager, Param_ServiceName, Param_DisplayName, 
			SERVICE_START|SERVICE_STOP, 
			SERVICE_KERNEL_DRIVER, SERVICE_DEMAND_START, SERVICE_ERROR_IGNORE,
			Param_DriverPath, nullptr, nullptr, nullptr, nullptr, nullptr);
		if(!TheService)
			return FormatError(L"%s: %s", L"Could not create the service", GetLastErrorStr());

	//Open the service if not creating
	} else {
		TheService=OpenService(HSCManager, Param_ServiceName, SERVICE_START|SERVICE_STOP);
		if(!TheService)
			return FormatError(L"%s: %s", L"Could not open the service", GetLastErrorStr());
	}
	SmartCloseService CloseTheService(&TheService, Delete_SmartCloseService); //Close the service on exit

	//Start the service
	wprintf(L"%s\n", L"Starting the service");
	for(INT_PTR i=0;i<2;i++)
	{
		if(StartService(TheService, 0, nullptr))
			break;

		//If not "service already running" error, or user does not want to stop the current service
		if(i==1 || GetLastError()!=ERROR_SERVICE_ALREADY_RUNNING || !AskQuestion(L"The service is already running. Would you like to stop it? (y/n)"))
			return FormatError(L"%s: %s", L"Could not start the service", GetLastErrorStr());

		//Stop the service
		SERVICE_STATUS ss;
		wprintf(L"%s\n", L"Stopping the current service");
		if(!ControlService(TheService, SERVICE_CONTROL_STOP, &ss))
			return FormatError(L"%s: %s", L"Could not stop the current service", GetLastErrorStr());
	}
	wprintf(L"%s\n", L"Started the service");

	//Ask if the user wants to close the service
	if(!AskQuestion(L"Would you like to stop the service before exit? (y/n)"))
		return nullptr;

	//Stop the service
	SERVICE_STATUS ss;
	if(!ControlService(TheService, SERVICE_CONTROL_STOP, &ss))
		return FormatError(L"%s: %s", L"Could not stop the service", GetLastErrorStr());
	if(ss.dwCurrentState!=SERVICE_STOP_PENDING && ss.dwCurrentState!=SERVICE_STOPPED)
		return FormatError(L"%s", L"The service does not appear to be closing");
	wprintf(L"%s\n", L"The service has been stopped");

	//Return success
	return nullptr;
}

WCHAR* FormatError(WCHAR* Format, ...)
{
	static WCHAR Err[255];
	va_list VAList;
	va_start(VAList, Format);
	vswprintf(Err, sizeof(Err)/sizeof(Err[0]), Format, VAList);
	return Err;
}

SmartWinAlloc GetLastErrorStr()
{
	LPWSTR MessageBuffer=nullptr;
	FormatMessage(
		FORMAT_MESSAGE_ALLOCATE_BUFFER|FORMAT_MESSAGE_FROM_SYSTEM|FORMAT_MESSAGE_IGNORE_INSERTS|FORMAT_MESSAGE_MAX_WIDTH_MASK,
		nullptr, GetLastError(), MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), (LPWSTR)&MessageBuffer, 0, nullptr);
	return SmartWinAlloc(MessageBuffer, Delete_SmartWinAlloc);
}

BOOLEAN AskQuestion(WCHAR* Question)
{
	//Make the request and wait for an input character
	while(1)
	{
		//Ask the question and get the answer
		wprintf(L"%s:", Question);
		fflush(stdout);
		char InputChar=_getch();
		printf("\n");

		//Check for a valid answer
		if(InputChar=='n' || InputChar=='N')
			return FALSE;
		if(InputChar=='y' || InputChar=='Y')
			return TRUE;
	}
}

The PHP MySQL extension is being deprecated in favor of the MySQLi extension in PHP 5.5, and removed as of PHP 7.0. MySQLi was first referenced in PHP v5.0.0 beta 4 on 2004-02-12, with the first stable release in PHP 5.0.0 on 2004-07-13^[1]. Before that, the PHP MySQL extension was by far the most popular way of interacting with MySQL on PHP, and still was for a very long time after. This website was opened only 2 years after the first stable release!

With the deprecation, problems from some websites I help host have popped up, many of these sites being very, very old. I needed a quick and dirty solution to monkey-patch these websites to use MySQLi without rewriting all their code. The obvious answer is to overwrite the functions with wrappers for MySQLi. The generally known way of doing this is with the Advanced PHP Debugger (APD). However, using this extension has a lot of requirements that are not appropriate for a production web server. Fortunately, another extension I recently learned of offers the renaming functionality; runkit. It was a super simple install for me.

1. From the command line, run “pecl install runkit”
2. Add “extension=runkit.so” and “runkit.internal_override=On” to the php.ini

Besides the ability to override these functions with wrappers, I also needed a way to make sure this file was always loaded before all other PHP files. The simple solution for that is adding “auto_prepend_file=/PATH/TO/FILE” to the “.user.ini” in the user’s root web directory.

The code for this script is as follows. It only contains a limited set of the MySQL functions, including some very esoteric ones that the web site used. This is not a foolproof script, but it gets the job done.

//Override the MySQL functions
foreach(Array(
    'connect', 'error', 'fetch_array', 'fetch_row', 'insert_id', 'num_fields', 'num_rows',
    'query', 'select_db', 'field_len', 'field_name', 'field_type', 'list_dbs', 'list_fields',
    'list_tables', 'tablename'
) as $FuncName)
    runkit_function_redefine("mysql_$FuncName", '',
        'return call_user_func_array("mysql_'.$FuncName.'_OVERRIDE", func_get_args());');

//If a connection is not explicitely passed to a mysql_ function, use the last created connection
global $SQLLink; //The remembered SQL Link
function GetConn($PassedConn)
{
    if(isset($PassedConn))
        return $PassedConn;
    global $SQLLink;
    return $SQLLink;
}

//Override functions
function mysql_connect_OVERRIDE($Host, $Username, $Password) {
    global $SQLLink;
    return $SQLLink=mysqli_connect($Host, $Username, $Password);
}
function mysql_error_OVERRIDE($SQLConn=NULL) {
    return mysqli_error(GetConn($SQLConn));
}
function mysql_fetch_array_OVERRIDE($Result, $ResultType=MYSQL_BOTH) {
    return mysqli_fetch_array($Result, $ResultType);
}
function mysql_fetch_row_OVERRIDE($Result) {
    return mysqli_fetch_row($Result);
}
function mysql_insert_id_OVERRIDE($SQLConn=NULL) {
    return mysqli_insert_id(GetConn($SQLConn));
}
function mysql_num_fields_OVERRIDE($Result) {
    return mysqli_num_fields($Result);
}
function mysql_num_rows_OVERRIDE($Result) {
    return mysqli_num_rows($Result);
}
function mysql_query_OVERRIDE($Query, $SQLConn=NULL) {
    return mysqli_query(GetConn($SQLConn), $Query);
}
function mysql_select_db_OVERRIDE($DBName, $SQLConn=NULL) {
    return mysqli_select_db(GetConn($SQLConn), $DBName);
}
function mysql_field_len_OVERRIDE($Result, $Offset) {
    $Fields=$Result->fetch_fields();
    return $Fields[$Offset]->length;
}
function mysql_field_name_OVERRIDE($Result, $Offset) {
    $Fields=$Result->fetch_fields();
    return $Fields[$Offset]->name;
}
function mysql_field_type_OVERRIDE($Result, $Offset) {
    $Fields=$Result->fetch_fields();
    return $Fields[$Offset]->type;
}
function mysql_list_dbs_OVERRIDE($SQLConn=NULL) {
    $Result=mysql_query('SHOW DATABASES', GetConn($SQLConn));
    $Tables=Array();
    while($Row=mysqli_fetch_assoc($Result))
        $Tables[]=$Row['Database'];
    return $Tables;
}
function mysql_list_fields_OVERRIDE($DBName, $TableName, $SQLConn=NULL) {
    $SQLConn=GetConn($SQLConn);
    $CurDB=mysql_fetch_array(mysql_query('SELECT Database()', $SQLConn));
    $CurDB=$CurDB[0];
    mysql_select_db($DBName, $SQLConn);
    $Result=mysql_query("SHOW COLUMNS FROM $TableName", $SQLConn);
    mysql_select_db($CurDB, $SQLConn);
    if(!$Result) {
        print 'Could not run query: '.mysql_error($SQLConn);
        return Array();
    }
    $Fields=Array();
    while($Row=mysqli_fetch_assoc($Result))
        $Fields[]=$Row['Field'];
    return $Fields;
}
function mysql_list_tables_OVERRIDE($DBName, $SQLConn=NULL) {
    $SQLConn=GetConn($SQLConn);
    $CurDB=mysql_fetch_array(mysql_query('SELECT Database()', $SQLConn));
    $CurDB=$CurDB[0];
    mysql_select_db($DBName, $SQLConn);
    $Result=mysql_query("SHOW TABLES", $SQLConn);
    mysql_select_db($CurDB, $SQLConn);
    if(!$Result) {
        print 'Could not run query: '.mysql_error($SQLConn);
        return Array();
    }
    $Tables=Array();
    while($Row=mysql_fetch_row($Result))
        $Tables[]=$Row[0];
    return $Tables;
}
function mysql_tablename_OVERRIDE($Result) {
    $Fields=$Result->fetch_fields();
    return $Fields[0]->table;
}

global $MyConn, $TEST_Table;
$TEST_Server='localhost';
$TEST_UserName='...';
$TEST_Password='...';
$TEST_DB='...';
$TEST_Table='...';
function GetResult() {
    global $MyConn, $TEST_Table;
    return mysql_query('SELECT * FROM '.$TEST_Table.' LIMIT 1', $MyConn);
}
var_dump($MyConn=mysql_connect($TEST_Server, $TEST_UserName, $TEST_Password));
//Set $MyConn to NULL here if you want to test global $SQLLink functionality
var_dump(mysql_select_db($TEST_DB, $MyConn));
var_dump(mysql_query('SELECT * FROM INVALIDTABLE LIMIT 1', $MyConn));
var_dump(mysql_error($MyConn));
var_dump($Result=GetResult());
var_dump(mysql_fetch_array($Result));
$Result=GetResult(); var_dump(mysql_fetch_row($Result));
$Result=GetResult(); var_dump(mysql_num_fields($Result));
var_dump(mysql_num_rows($Result));
var_dump(mysql_field_len($Result, 0));
var_dump(mysql_field_name($Result, 0));
var_dump(mysql_field_type($Result, 0));
var_dump(mysql_tablename($Result));
var_dump(mysql_list_dbs($MyConn));
var_dump(mysql_list_fields($TEST_DB, $TEST_Table, $MyConn));
var_dump(mysql_list_tables($TEST_DB, $MyConn));
mysql_query('CREATE TEMPORARY TABLE mysqltest (i int auto_increment, primary key (i))', $MyConn);
mysql_query('INSERT INTO mysqltest VALUES ()', $MyConn);
mysql_query('INSERT INTO mysqltest VALUES ()', $MyConn);
var_dump(mysql_insert_id($MyConn));
mysql_query('DROP TEMPORARY TABLE mysqltest', $MyConn);

• Takes raw input and wraps long lines to indent against the current line start.
• When an indented/list line is encountered, which starts with spaces followed by a star "*", wrapped line’s indents will start 2 spaces after the star.
• Lines attempt to split at words of 10 characters or less (see .MinCharsInSplitWord).
• If a line needs to split along a word longer than this, a hyphen is inserted at the end of the line.

import argparse
import re
class BulletHelpFormatter(argparse.HelpFormatter):
    def __init__(self, *args, **kwargs):
        super(BulletHelpFormatter, self).__init__(*args, **kwargs)
        self.MinCharsInSplitWord=10

    def _split_lines(self, text, width):
        #Split lines around line breaks and then modify each line
        Lines=[]
        for Line in text.splitlines():
            #Get the number of spaces to put at subsequent lines
            #0 if not a list item, oherwise, 2+list item start
            ListEl=re.match(r'^ *\*', Line)
            NumBeginningSpace=(0 if ListEl==None else ListEl.end()+1)

            #Add extra spaces at the beginning of each line to match the start of the current line, and go to a maxium of $width
            IsFirstPass=True
            SpacesToAdd=''
            NumSpacesToAdd=0
            while(True):
                #Get the word break points before and after where the line would end
                MaxLineLen=max(min(width-NumSpacesToAdd, len(Line)), 1)
                PrevWordBreak=CurWordBreak=0
                for WordBreak in re.finditer(r'(?<=\W).|\W|$', Line):
                    PrevWordBreak=CurWordBreak
                    CurWordBreak=WordBreak.start()
                    if CurWordBreak>=MaxLineLen:
                        if CurWordBreak==MaxLineLen:
                            PrevWordBreak=CurWordBreak
                        break

                #If previous wordbreak is more than MinCharsInSplitWord away from MaxLineLen, then split at the end of the line
                IsSplit=(PrevWordBreak<1 or CurWordBreak-PrevWordBreak>self.MinCharsInSplitWord)
                SplitPos=(MaxLineLen if IsSplit else PrevWordBreak)

                #Append the new line to the list of lines
                Lines.append(SpacesToAdd+Line[0:SplitPos]+('-' if IsSplit else ''))
                Line=Line[SplitPos:]

                #If this is the end, nothing left to do
                if len(Line)==0:
                    break

                #If this is the first pass, update line creation variables
                if IsFirstPass:
                    IsFirstPass=False
                    NumSpacesToAdd=NumBeginningSpace
                    SpacesToAdd=(' ' * NumSpacesToAdd)

        return Lines

I had the need for a project recently to have a database list of countries and currencies. I ended up pulling the currency list from wikipedia and the country list from the stage.gov website. You can download the SQL here, or copy from below. Do note there are NULLs in some of the places where data was unavailable.

DROP TABLE IF EXISTS Countries;
CREATE TABLE Countries (
  ID tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  FIPS char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  Name varchar(35) NOT NULL,
  LongName varchar(55) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY FIPS (FIPS),
  UNIQUE KEY Name (Name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Countries (FIPS, Name, LongName) VALUES
  ('AF','Afghanistan','Islamic Republic of Afghanistan'),
  ('AL','Albania','Republic of Albania'),
  ('AG','Algeria','People’s Democratic Republic of Algeria'),
  ('AN','Andorra','Principality of Andorra'),
  ('AO','Angola','Republic of Angola'),
  ('AC','Antigua and Barbuda','Antiqua and Barbuda'),
  ('AR','Argentina','Argentine Republic'),
  ('AM','Armenia','Republic of Armenia'),
  ('AS','Australia','Commonwealth of Australia'),
  ('AU','Austria','Republic of Austria'),
  ('AJ','Azerbaijan','Republic of Azerbaijan'),
  ('BF','Bahamas, The','Commonwealth of The Bahamas'),
  ('BA','Bahrain','Kingdom of Bahrain'),
  ('BG','Bangladesh','People’s Republic of Bangladesh'),
  ('BB','Barbados','Barbados'),
  ('BO','Belarus','Republic of Belarus'),
  ('BE','Belgium','Kingdom of Belgium'),
  ('BH','Belize','Belize'),
  ('BN','Benin','Republic of Benin'),
  ('BT','Bhutan','Kingdom of Bhutan'),
  ('BL','Bolivia','Plurinational State of Bolivia'),
  ('BK','Bosnia and Herzegovina','Bosnia and Herzegovina'),
  ('BC','Botswana','Republic of Botswana'),
  ('BR','Brazil','Federative Republic of Brazil'),
  ('BX','Brunei','Brunei Darussalam'),
  ('BU','Bulgaria','Republic of Bulgaria'),
  ('UV','Burkina Faso','Burkina Faso'),
  ('BM','Burma','Union of Burma'),
  ('BY','Burundi','Republic of Burundi'),
  ('CV','Cabo Verde','Republic of Cabo Verde'),
  ('CB','Cambodia','Kingdom of Cambodia'),
  ('CM','Cameroon','Republic of Cameroon'),
  ('CA','Canada','Canada'),
  ('CT','Central African Republic','Central African Republic'),
  ('CD','Chad','Republic of Chad'),
  ('CI','Chile','Republic of Chile'),
  ('CH','China','People’s Republic of China'),
  ('CO','Colombia','Republic of Colombia'),
  ('CN','Comoros','Union of the Comoros'),
  ('CF','Congo (Brazzaville)','Republic of the Congo'),
  ('CG','Congo (Kinshasa)','Democratic Republic of the Congo'),
  ('CS','Costa Rica','Republic of Costa Rica'),
  ('IV','Côte d’Ivoire','Republic of Côte d’Ivoire'),
  ('HR','Croatia','Republic of Croatia'),
  ('CU','Cuba','Republic of Cuba'),
  ('CY','Cyprus','Republic of Cyprus'),
  ('EZ','Czech Republic','Czech Republic'),
  ('DA','Denmark','Kingdom of Denmark'),
  ('DJ','Djibouti','Republic of Djibouti'),
  ('DO','Dominica','Commonwealth of Dominica'),
  ('DR','Dominican Republic','Dominican Republic'),
  ('EC','Ecuador','Republic of Ecuador'),
  ('EG','Egypt','Arab Republic of Egypt'),
  ('ES','El Salvador','Republic of El Salvador'),
  ('EK','Equatorial Guinea','Republic of Equatorial Guinea'),
  ('ER','Eritrea','State of Eritrea'),
  ('EN','Estonia','Republic of Estonia'),
  ('ET','Ethiopia','Federal Democratic Republic of Ethiopia'),
  ('FJ','Fiji','Republic of Fiji'),
  ('FI','Finland','Republic of Finland'),
  ('FR','France','French Republic'),
  ('GB','Gabon','Gabonese Republic'),
  ('GA','Gambia, The','Republic of The Gambia'),
  ('GG','Georgia','Georgia'),
  ('GM','Germany','Federal Republic of Germany'),
  ('GH','Ghana','Republic of Ghana'),
  ('GR','Greece','Hellenic Republic'),
  ('GJ','Grenada','Grenada'),
  ('GT','Guatemala','Republic of Guatemala'),
  ('GV','Guinea','Republic of Guinea'),
  ('PU','Guinea-Bissau','Republic of Guinea-Bissau'),
  ('GY','Guyana','Co-operative Republic of Guyana'),
  ('HA','Haiti','Republic of Haiti'),
  ('VT','Holy See','Holy See'),
  ('HO','Honduras','Republic of Honduras'),
  ('HU','Hungary','Hungary'),
  ('IC','Iceland','Republic of Iceland'),
  ('IN','India','Republic of India'),
  ('ID','Indonesia','Republic of Indonesia'),
  ('IR','Iran','Islamic Republic of Iran'),
  ('IZ','Iraq','Republic of Iraq'),
  ('EI','Ireland','Ireland'),
  ('IS','Israel','State of Israel'),
  ('IT','Italy','Italian Republic'),
  ('JM','Jamaica','Jamaica'),
  ('JA','Japan','Japan'),
  ('JO','Jordan','Hashemite Kingdom of Jordan'),
  ('KZ','Kazakhstan','Republic of Kazakhstan'),
  ('KE','Kenya','Republic of Kenya'),
  ('KR','Kiribati','Republic of Kiribati'),
  ('KN','Korea, North','Democratic People’s Republic of Korea'),
  ('KS','Korea, South','Republic of Korea'),
  ('KV','Kosovo','Republic of Kosovo'),
  ('KU','Kuwait','State of Kuwait'),
  ('KG','Kyrgyzstan','Kyrgyz Republic'),
  ('LA','Laos','Lao People’s Democratic Republic'),
  ('LG','Latvia','Republic of Latvia'),
  ('LE','Lebanon','Lebanese Republic'),
  ('LT','Lesotho','Kingdom of Lesotho'),
  ('LI','Liberia','Republic of Liberia'),
  ('LY','Libya','Libya'),
  ('LS','Liechtenstein','Principality of Liechtenstein'),
  ('LH','Lithuania','Republic of Lithuania'),
  ('LU','Luxembourg','Grand Duchy of Luxembourg'),
  ('MK','Macedonia','Republic of Macedonia'),
  ('MA','Madagascar','Republic of Madagascar'),
  ('MI','Malawi','Republic of Malawi'),
  ('MY','Malaysia','Malaysia'),
  ('MV','Maldives','Republic of Maldives'),
  ('ML','Mali','Republic of Mali'),
  ('MT','Malta','Republic of Malta'),
  ('RM','Marshall Islands','Republic of the Marshall Islands'),
  ('MR','Mauritania','Islamic Republic of Mauritania'),
  ('MP','Mauritius','Republic of Mauritius'),
  ('MX','Mexico','United Mexican States'),
  ('FM','Micronesia, Federated States of','Federated States of Micronesia'),
  ('MD','Moldova','Republic of Moldova'),
  ('MN','Monaco','Principality of Monaco'),
  ('MG','Mongolia','Mongolia'),
  ('MJ','Montenegro','Montenegro'),
  ('MO','Morocco','Kingdom of Morocco'),
  ('MZ','Mozambique','Republic of Mozambique'),
  ('WA','Namibia','Republic of Namibia'),
  ('NR','Nauru','Republic of Nauru'),
  ('NP','Nepal','Federal Democratic Republic of Nepal'),
  ('NL','Netherlands','Kingdom of the Netherlands'),
  ('NZ','New Zealand','New Zealand'),
  ('NU','Nicaragua','Republic of Nicaragua'),
  ('NG','Niger','Republic of Niger'),
  ('NI','Nigeria','Federal Republic of Nigeria'),
  ('NO','Norway','Kingdom of Norway'),
  ('MU','Oman','Sultanate of Oman'),
  ('PK','Pakistan','Islamic Republic of Pakistan'),
  ('PS','Palau','Republic of Palau'),
  ('PM','Panama','Republic of Panama'),
  ('PP','Papua New Guinea','Independent State of Papua New Guinea'),
  ('PA','Paraguay','Republic of Paraguay'),
  ('PE','Peru','Republic of Peru'),
  ('RP','Philippines','Republic of the Philippines'),
  ('PL','Poland','Republic of Poland'),
  ('PO','Portugal','Portuguese Republic'),
  ('QA','Qatar','State of Qatar'),
  ('RO','Romania','Romania'),
  ('RS','Russia','Russian Federation'),
  ('RW','Rwanda','Republic of Rwanda'),
  ('SC','Saint Kitts and Nevis','Federation of Saint Kitts and Nevis'),
  ('ST','Saint Lucia','Saint Lucia'),
  ('VC','Saint Vincent and the Grenadines','Saint Vincent and the Grenadines'),
  ('WS','Samoa','Independent State of Samoa'),
  ('SM','San Marino','Republic of San Marino'),
  ('TP','Sao Tome and Principe','Democratic Republic of Sao Tome and Principe'),
  ('SA','Saudi Arabia','Kingdom of Saudi Arabia'),
  ('SG','Senegal','Republic of Senegal'),
  ('RI','Serbia','Republic of Serbia'),
  ('SE','Seychelles','Republic of Seychelles'),
  ('SL','Sierra Leone','Republic of Sierra Leone'),
  ('SN','Singapore','Republic of Singapore'),
  ('LO','Slovakia','Slovak Republic'),
  ('SI','Slovenia','Republic of Slovenia'),
  ('BP','Solomon Islands','Solomon Islands'),
  ('SO','Somalia','Federal Republic of Somalia'),
  ('SF','South Africa','Republic of South Africa'),
  ('OD','South Sudan','Republic of South Sudan'),
  ('SP','Spain','Kingdom of Spain'),
  ('CE','Sri Lanka','Democratic Socialist Republic of Sri Lanka'),
  ('SU','Sudan','Republic of the Sudan'),
  ('NS','Suriname','Republic of Suriname'),
  ('WZ','Swaziland','Kingdom of Swaziland'),
  ('SW','Sweden','Kingdom of Sweden'),
  ('SZ','Switzerland','Swiss Confederation'),
  ('SY','Syria','Syrian Arab Republic'),
  ('TI','Tajikistan','Republic of Tajikistan'),
  ('TZ','Tanzania','United Republic of Tanzania'),
  ('TH','Thailand','Kingdom of Thailand'),
  ('TT','Timor-Leste','Democratic Republic of Timor-Leste'),
  ('TO','Togo','Togolese Republic'),
  ('TN','Tonga','Kingdom of Tonga'),
  ('TD','Trinidad and Tobago','Republic of Trinidad and Tobago'),
  ('TS','Tunisia','Republic of Tunisia'),
  ('TU','Turkey','Republic of Turkey'),
  ('TX','Turkmenistan','Turkmenistan'),
  ('TV','Tuvalu','Tuvalu'),
  ('UG','Uganda','Republic of Uganda'),
  ('UP','Ukraine','Ukraine'),
  ('AE','United Arab Emirates','United Arab Emirates'),
  ('UK','United Kingdom','United Kingdom of Great Britain and Northern Ireland'),
  ('US','United States','United States of America'),
  ('UY','Uruguay','Oriental Republic of Uruguay'),
  ('UZ','Uzbekistan','Republic of Uzbekistan'),
  ('NH','Vanuatu','Republic of Vanuatu'),
  ('VE','Venezuela','Bolivarian Republic of Venezuela'),
  ('VM','Vietnam','Socialist Republic of Vietnam'),
  ('YM','Yemen','Republic of Yemen'),
  ('ZA','Zambia','Republic of Zambia'),
  ('ZI','Zimbabwe','Republic of Zimbabwe');

DROP TABLE IF EXISTS Currencies;
CREATE TABLE Currencies (
  ID tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  ISOCode char(3) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  NumberToBasic smallint(5) unsigned DEFAULT NULL,
  Symbol varchar(6) DEFAULT NULL,
  Name varchar(55) DEFAULT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY ISOCode (ISOCode),
  UNIQUE KEY Name (Name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Currencies (ISOCode, NumberToBasic, Symbol, Name) VALUES
  ('AFN',100,'؋','Afghan afghani'),
  ('ALL',100,'L','Albanian lek'),
  (NULL,100,'£','Alderney pound'),
  ('DZD',100,'د.ج','Algerian dinar'),
  ('AOA',100,'Kz','Angolan kwanza'),
  ('ARS',100,'$','Argentine peso'),
  ('AMD',100,'֏','Armenian dram'),
  ('AWG',100,'ƒ','Aruban florin'),
  (NULL,100,'£','Ascension pound'),
  ('AUD',100,'$','Australian dollar'),
  ('AZN',100,'₼','Azerbaijani manat'),
  ('BSD',100,'$','Bahamian dollar'),
  ('BHD',1000,'.د.ب','Bahraini dinar'),
  ('BDT',100,'৳','Bangladeshi taka'),
  ('BBD',100,'$','Barbadian dollar'),
  ('BYR',100,'Br','Belarusian ruble'),
  ('BZD',100,'$','Belize dollar'),
  ('BMD',100,'$','Bermudian dollar'),
  ('BTN',100,'Nu.','Bhutanese ngultrum'),
  ('BOB',100,'Bs.','Bolivian boliviano'),
  ('BAM',100,'KM','Bosnia and Herzegovina convertible mark'),
  ('BWP',100,'P','Botswana pula'),
  ('BRL',100,'R$','Brazilian real'),
  ('GBP',100,'£','British pound'),
  (NULL,100,'$','British Virgin Islands dollar'),
  ('BND',100,'$','Brunei dollar'),
  ('BGN',100,'лв','Bulgarian lev'),
  ('MMK',100,'Ks','Burmese kyat'),
  ('BIF',100,'Fr','Burundian franc'),
  ('KHR',100,'៛','Cambodian riel'),
  ('CAD',100,'$','Canadian dollar'),
  ('CVE',100,'$','Cape Verdean escudo'),
  ('KYD',100,'$','Cayman Islands dollar'),
  ('XAF',100,'Fr','Central African CFA franc'),
  ('XPF',100,'Fr','CFP franc'),
  ('CLP',100,'$','Chilean peso'),
  ('CNY',100,'¥','Chinese yuan'),
  ('COP',100,'$','Colombian peso'),
  ('KMF',100,'Fr','Comorian franc'),
  ('CDF',100,'Fr','Congolese franc'),
  (NULL,100,'$','Cook Islands dollar'),
  ('CRC',100,'₡','Costa Rican colón'),
  ('HRK',100,'kn','Croatian kuna'),
  ('CUC',100,'$','Cuban convertible peso'),
  ('CUP',100,'$','Cuban peso'),
  ('CZK',100,'Kč','Czech koruna'),
  ('DKK',100,'kr','Danish krone'),
  ('DJF',100,'Fr','Djiboutian franc'),
  ('DOP',100,'$','Dominican peso'),
  ('XCD',100,'$','East Caribbean dollar'),
  ('EGP',100,'£','Egyptian pound'),
  ('ERN',100,'Nfk','Eritrean nakfa'),
  ('ETB',100,'Br','Ethiopian birr'),
  ('EUR',100,'€','Euro'),
  ('FKP',100,'£','Falkland Islands pound'),
  (NULL,100,'kr','Faroese króna'),
  ('FJD',100,'$','Fijian dollar'),
  ('GMD',100,'D','Gambian dalasi'),
  ('GEL',100,'ლ','Georgian lari'),
  ('GHS',100,'₵','Ghana cedi'),
  ('GIP',100,'£','Gibraltar pound'),
  ('GTQ',100,'Q','Guatemalan quetzal'),
  ('GGP',100,'£','Guernsey pound'),
  ('GNF',100,'Fr','Guinean franc'),
  ('GYD',100,'$','Guyanese dollar'),
  ('HTG',100,'G','Haitian gourde'),
  ('HNL',100,'L','Honduran lempira'),
  ('HKD',100,'$','Hong Kong dollar'),
  ('HUF',100,'Ft','Hungarian forint'),
  ('ISK',100,'kr','Icelandic króna'),
  ('INR',100,'₹','Indian rupee'),
  ('IDR',100,'Rp','Indonesian rupiah'),
  ('IRR',100,'﷼','Iranian rial'),
  ('IQD',1000,'ع.د','Iraqi dinar'),
  ('ILS',100,'₪','Israeli new shekel'),
  ('JMD',100,'$','Jamaican dollar'),
  ('JPY',100,'¥','Japanese yen'),
  ('JEP',100,'£','Jersey pound'),
  ('JOD',100,'د.ا','Jordanian dinar'),
  ('KZT',100,'₸','Kazakhstani tenge'),
  ('KES',100,'Sh','Kenyan shilling'),
  (NULL,100,'$','Kiribati dollar'),
  ('KWD',1000,'د.ك','Kuwaiti dinar'),
  ('KGS',100,'лв','Kyrgyzstani som'),
  ('LAK',100,'₭','Lao kip'),
  ('LBP',100,'ل.ل','Lebanese pound'),
  ('LSL',100,'L','Lesotho loti'),
  ('LRD',100,'$','Liberian dollar'),
  ('LYD',1000,'ل.د','Libyan dinar'),
  ('MOP',100,'P','Macanese pataca'),
  ('MKD',100,'ден','Macedonian denar'),
  ('MGA',5,'Ar','Malagasy ariary'),
  ('MWK',100,'MK','Malawian kwacha'),
  ('MYR',100,'RM','Malaysian ringgit'),
  ('MVR',100,'.ރ','Maldivian rufiyaa'),
  ('IMP',100,'£','Manx pound'),
  ('MRO',5,'UM','Mauritanian ouguiya'),
  ('MUR',100,'₨','Mauritian rupee'),
  ('MXN',100,'$','Mexican peso'),
  (NULL,100,'$','Micronesian dollar'),
  ('MDL',100,'L','Moldovan leu'),
  ('MNT',100,'₮','Mongolian tögrög'),
  ('MAD',100,'د. م.','Moroccan dirham'),
  ('MZN',100,'MT','Mozambican metical'),
  (NULL,100,'դր.','Nagorno-Karabakh dram'),
  ('NAD',100,'$','Namibian dollar'),
  (NULL,100,'$','Nauruan dollar'),
  ('NPR',100,'₨','Nepalese rupee'),
  ('ANG',100,'ƒ','Netherlands Antillean guilder'),
  ('TWD',100,'$','New Taiwan dollar'),
  ('NZD',100,'$','New Zealand dollar'),
  ('NIO',100,'C$','Nicaraguan córdoba'),
  ('NGN',100,'₦','Nigerian naira'),
  (NULL,100,'$','Niue dollar'),
  ('KPW',100,'₩','North Korean won'),
  ('NOK',100,'kr','Norwegian krone'),
  ('OMR',1000,'ر.ع.','Omani rial'),
  ('PKR',100,'₨','Pakistani rupee'),
  (NULL,100,'$','Palauan dollar'),
  ('PAB',100,'B/.','Panamanian balboa'),
  ('PGK',100,'K','Papua New Guinean kina'),
  ('PYG',100,'₲','Paraguayan guaraní'),
  ('PEN',100,'S/.','Peruvian nuevo sol'),
  ('PHP',100,'₱','Philippine peso'),
  (NULL,100,'$','Pitcairn Islands dollar'),
  ('PLN',100,'zł','Polish złoty'),
  ('QAR',100,'ر.ق','Qatari riyal'),
  ('RON',100,'lei','Romanian leu'),
  ('RUB',100,'₽','Russian ruble'),
  ('RWF',100,'Fr','Rwandan franc'),
  (NULL,100,'Ptas','Sahrawi peseta'),
  ('SHP',100,'£','Saint Helena pound'),
  ('WST',100,'T','Samoan tālā'),
  ('STD',100,'Db','São Tomé and Príncipe dobra'),
  ('SAR',100,'ر.س','Saudi riyal'),
  ('RSD',100,'дин.','Serbian dinar'),
  ('SCR',100,'₨','Seychellois rupee'),
  ('SLL',100,'Le','Sierra Leonean leone'),
  ('SGD',100,'$','Singapore dollar'),
  ('SBD',100,'$','Solomon Islands dollar'),
  ('SOS',100,'Sh','Somali shilling'),
  (NULL,100,'Sh','Somaliland shilling'),
  ('ZAR',100,'R','South African rand'),
  (NULL,100,'£','South Georgia and the South Sandwich Islands pound'),
  ('KRW',100,'₩','South Korean won'),
  ('SSP',100,'£','South Sudanese pound'),
  ('LKR',100,'රු','Sri Lankan rupee'),
  ('SDG',100,'ج.س.','Sudanese pound'),
  ('SRD',100,'$','Surinamese dollar'),
  ('SZL',100,'L','Swazi lilangeni'),
  ('SEK',100,'kr','Swedish krona'),
  ('CHF',100,'Fr','Swiss franc'),
  ('SYP',100,'£','Syrian pound'),
  ('TJS',100,'ЅМ','Tajikistani somoni'),
  ('TZS',100,'Sh','Tanzanian shilling'),
  ('THB',100,'฿','Thai baht'),
  ('TOP',100,'T$','Tongan paʻanga'),
  ('PRB',100,'р.','Transnistrian ruble'),
  ('TTD',100,'$','Trinidad and Tobago dollar'),
  (NULL,100,'£','Tristan da Cunha pound'),
  ('TND',1000,'د.ت','Tunisian dinar'),
  ('TRY',100,'₺','Turkish lira'),
  ('TMT',100,'m','Turkmenistan manat'),
  (NULL,100,'$','Tuvaluan dollar'),
  ('UGX',100,'Sh','Ugandan shilling'),
  ('UAH',100,'₴','Ukrainian hryvnia'),
  ('AED',100,'د.إ','United Arab Emirates dirham'),
  ('USD',100,'$','United States dollar'),
  ('UYU',100,'$','Uruguayan peso'),
  ('UZS',100,NULL,'Uzbekistani som'),
  ('VUV',NULL,'Vt','Vanuatu vatu'),
  ('VEF',100,'Bs F','Venezuelan bolívar'),
  ('VND',10,'₫','Vietnamese đồng'),
  ('XOF',100,'Fr','West African CFA franc'),
  ('YER',100,'﷼','Yemeni rial'),
  ('ZMW',100,'ZK','Zambian kwacha');

So somehow all of the file names in my Rammstein music directory, and some in my Daft Punk, had characters with diacritics replaced with an invalid character. I pasted one of such filenames into a hex editor to evaluate what the problem was. First, I should note that Windows encodes its filenames (and pretty much everything) in UTF16. Everything else in the world (mostly) has settled on UTF8, which is a much better encoding for many reasons. So during some file copy/conversion at some point in the directories’ lifetime, the file names had done a freakish (utf16*)(utf16->utf8) rename, or something to that extent. I had noticed that all I needed to do was to replace the first 2 bytes of the diacritic character with a different byte. Namely “EF 8x” to “Cx”, and the rest of the bytes for the character were fine. So if anyone ever needs it, here is the bash script.

LANG=;
IFS=$'\n'
for i in `find -type f | grep -P '\xEF[\x80-\x8F]'`; do
	FROM="$i";
	TO=$(echo "$i" | perl -pi -e 's/\xEF([\x80-\x8F])/pack("C", ord($1)+(0xC0-0x80))/e');
	echo Renaming "'$FROM'" to "'$TO'"
	mv "$FROM" "$TO"
done

I may need to expand the range beyond the x80-x8F range, but am unsure at this point. I only confirmed the range x82-x83.

While I’ve been encountering more bugs than I can count on both hands while working with XCode, this one takes the cake. Clang (the compiler) was throwing the following errors while it was trying to compile one of its objective C source files (.m extension).

clang: error: unable to execute command: Segmentation fault: 11
clang: error: clang frontend command failed due to signal (use -v to see invocation)
Apple LLVM version 7.0.2 (clang-700.1.81)
Target: arm-apple-darwin14.5.0
Thread model: posix
clang: note: diagnostic msg: PLEASE submit a bug report to http://developer.apple.com/bugreporter/ and include the crash backtrace, preprocessed source, and associated run script.
clang: error: unable to execute command: Segmentation fault: 11
clang: note: diagnostic msg: Error generating preprocessed source(s).

The fix... was to keep the specific source file open in an XCode window ~.~ . How the heck do you integrate the [CLI] compiler so much into the IDE that this could happen? Or is this simply a weird file system thing? I should note that my XCode project directory, with all files, is located on a VMware volume share.

One of the companies I work for recently moved one of our cPanel servers to a new collocation, still running cPanel. We decided to use a new backup solution called r1soft, which so far has been working spectacularly. I’d love to use it for my personal computers, except the licenses, which are geared towards enterprise business, are way too costly.

However, since r1soft only backs up files (on the incrementally block level, yay) you can’t use it to restore a cPanel account. It can only restore things like the user’s home directory and SQL databases. Because of this, when we had need to restore an entire account today, and found out there is no easy/quick way to do it, we were up a creek. The obvious future solution for this would be to use cPanel’s backup (or legacy backup) systems, but unfortunately, you can’t easily set them to not backup the user’s databases and home directory, which can be very large, and are already taken care of by r1soft. I ended up adding the following script, ran nightly via cron, to back up user account settings.

It saves all the user settings under the backup path in their own directory, uncompressed, and named cpmove-USERNAME. It is best to do it this way so r1soft’s incremental backups don’t have much extra work if anything changes. Make sure to change line 3 in the following script to the path where you want backups to occur.

#!/bin/bash
#Create and move to backup directory
BACKUPDIR=/backup/userbackup
mkdir -p $BACKUPDIR #Make sure the directory exists
cd $BACKUPDIR

#Remove old backups
rm -rf cpmove-*

#Loop over accounts
for USER in `/usr/sbin/whmapi1 listaccts | grep -oP '(?<=user: )\w+$' | sort -u`; do
  #Backup the account
  /scripts/pkgacct --nocompress --skipbwdata --skiphomedir --skiplogs --skipmysql --skipmailman $USER ./

  #Extract from and remove the tar container file
  tar -xvf cpmove-$USER.tar
  rm -f cpmove-$USER.tar

  #Save MySQL user settings
  mysqldump --compact -fnt -w "User LIKE '$USER""_%'" mysql user db tables_priv columns_priv procs_priv proxies_priv \
  | perl -pe "s~('|NULL)\),\('~\1),\n('~ig" \
  > cpmove-$USER/mysql-users.sql
done;

This script skips a few backup items that need to be noted. Mailman, logs, homedir, and bandwidth data should all be easy 1:1 copy over restores from r1soft. I excluded them because those can take up a lot of room, which we want r1soft to handle. The same goes for MySQL, except that your MySQL users are not backed up to your account, which is why I added the final section.

Do note, for the final section, the line starting with “| perl” is optional. It is there to separate the insert rows into their own lines. A very minor warning though; it would also pick up cases where the last field in MySQL’s user table ends in “NULL),​(”. This would only happen if someone is trying to be malicious and knew about this script, and even then, it couldn’t harm anything.

Bonus note: To restore a MySQL database which does not use a shared-file (like InnoDB does by default), you could actually stop the MySQL server, copy over the binary database files, and start the server back up.

When I first created my website 10 years ago, from scratch, I did not want to deal with writing a comment system with HTML markups. And in those days, there weren’t plugins for everything like there is today. My solution was setting up a forum which would contain a topic for every Project, Update, and Post, and have my pages mirror the linked topic’s posts.

I had just put in a quick hack at the time in which the pulled SMF message’s body had links converted from bbcode (there might have been 1 other bbcode I also hooked). I had done this with regular expressions, which was a nasty hack.

So anywho, I finally got around to writing a script that converts SMF messages’ bbcode to HTML and caches it. You can download it here, or see the code below. The script is optimized so that it only ever needs to load SMF code when a post has not yet been cached. Caching happens during the initial loading of an SMF post within the script’s main function, and is discarded if the post is changed.

The script requires that you run the query on line #3 of itself in your SMF database. Directly after that are 3 variables you need to set. The script assumes you are already logged in to the appropriate user. To use it, call “GFTP\GetForumTopicPosts($ForumTopicID)”. I have the functions split up so you can do individual posts too if needed (requires a little extra code).

<?
//This SQL command must be ran before using the script
//ALTER TABLE smf_messages ADD body_html text, ADD body_md5 char(32) DEFAULT NULL;

namespace GFTP;

//Forum database variables
global $ForumInfo;
$ForumInfo=Array(
    'DBName'=>'YourDatabase_smf',
    'Location'=>'/home/YourUser/www',
    'MessageTableName'=>'smf2_messages',
);

function GetForumTopicPosts($ForumTopicID)
{
    //Change to the forum database
    global $ForumInfo;
    $CurDB=mysql_fetch_row(mysql_query('SELECT database()'))[0];
    if($CurDB!=$ForumInfo['DBName'])
        mysql_select_db($ForumInfo['DBName']);
    $OldEncoding=SetEncoding(true);

    //Get the posts
    $PostsInfos=Array();
    $PostsQuery=mysql_query('SELECT '.implode(', ', PostFields())." FROM $ForumInfo[MessageTableName] WHERE id_topic='".intval($ForumTopicID).
        "' AND approved=1 ORDER BY id_msg ASC LIMIT 1, 9999999");
    if($PostsQuery) //If query failed, do not process
        while(($PostInfo=mysql_fetch_assoc($PostsQuery)) && ($PostsInfos[]=$PostInfo))
            if(md5($PostInfo['body'])!=$PostInfo['body_md5']) //If the body md5s do not match, get new value, otherwise, use cached value
                ProcessPost($PostsInfos[count($PostsInfos)-1]); //Process the lastest post as a reference

    //Restore from the forum database
    if($CurDB!=$ForumInfo['DBName'])
        mysql_select_db($CurDB);
    SetEncoding(false, $OldEncoding);

    //Return the posts
    return $PostsInfos;
}

function ProcessPost(&$PostInfo) //PostInfo must have fields id_msg, body, body_md5, and body_html
{
    //Load SMF
    global $ForumInfo;
    if(!defined('SMF'))
    {
        global $context;
        require_once(rtrim($ForumInfo['Location'], DIRECTORY_SEPARATOR).DIRECTORY_SEPARATOR.'SSI.php');
        mysql_select_db($ForumInfo['DBName']);
        SetEncoding();
    }

    //Update the cached body_html field
    $ParsedCode=$PostInfo['body_html']=parse_bbc($PostInfo['body']);
    $EscapedHTMLBody=mysql_escape_string($ParsedCode);
    $BodyMD5=md5($PostInfo['body']);
    mysql_query("UPDATE $ForumInfo[MessageTableName] SET body_html='$EscapedHTMLBody', body_md5='$BodyMD5' WHERE id_msg=$PostInfo[id_msg]");
}

//The fields to select in the Post query
function PostFields() { return Array('id_msg', 'poster_time', 'id_member', 'subject', 'poster_name', 'body', 'body_md5', 'body_html'); }

//Swap character encodings. Needs to be set to utf8
function SetEncoding($GetOld=false, $NewSet=Array('utf8', 'utf8', 'utf8'))
{
    //Get the old charset if required
    $CharacterVariables=Array('character_set_client', 'character_set_results', 'character_set_connection');
    $OldSet=Array();
    if($GetOld)
    {
        //Fill in variables with default in case they are not found
        foreach($CharacterVariables as $Index => $Variable)
            $OldSet[$Variable]='utf8';

        //Query for the character sets and update the OldSet array
        $Query=mysql_query('SHOW VARIABLES LIKE "character_%"');
        while($VariableInfo=mysql_fetch_assoc($Query))
            if(isset($OldSet[$VariableInfo['Variable_name']]))
                $OldSet[$VariableInfo['Variable_name']]=$VariableInfo['Value'];

        $OldSet=array_values($OldSet); //Turn back into numerical array
    }

    //Change to the new database encoding
    $CompiledSets=Array();
    foreach($CharacterVariables as $Index => $Variable)
        $CompiledSets[$Index]=$CharacterVariables[$Index].'="'.mysql_escape_string($NewSet[$Index]).'"';
    mysql_query('SET '.implode(', ', $CompiledSets));

    //If requested, return the previous values
    return $OldSet;
}
?>

Amazon EC2 is a great resource for cheap virtual servers to do simple things, like DNS or (low bandwidth) VPNs. I had the need this morning to set up a DNS server for a company which needed to blacklist a list of domains. The simplest way to do this is by editing all the computers’ hostfiles, but that method leaves a lot to be desired. Namely, blocking entire domains (as opposed to single subdomains), and deploying changes. Centralizing in a single place makes the job instant, immediate, and in the end, faster.

The following are the steps I used to set this up on an EC2 server. All command line instructions are followed by a single command you can run to execute the step. There is a full script below, at the end of the post, containing all steps from when you first login to SSH ("Login to root") to the end.

I am not going to go into the details of setting up an EC2 instance, as that information can be found elsewhere. I will also be skipping over some of the more obvious steps. Just create a default EC2 instance with the “Amazon Linux AMI”, and I will list all the changes that need to be made beyond that.

• Creating the instance
  • For the first year, for the instance type, you might as well use a t2.micro, as it is free. After that, a t2.nano (which is a new lower level) currently at $56.94/year ($0.0065/Hour), should be fine.
  • After you select your instance type, click “Review and Launch” to launch the instance with all of the defaults.
  • After the confirmation screen, it will ask you to create a key pair. You can see other tutorials about this and how it enables you to log into your instance.
• Edit the security group
  • Next, you need to edit the security group for your instance to allow incoming connections.
  • Go to “Instances” under the “Instances” group on the left menu, and click your instance.
  • In the bottom of the window, in the “Descriptions” tab, click the link next to “Security Groups”, which will bring you to the proper group in the security groups tab.
  • Right click it and “Edit inbound Rules”.
  • Make sure it has the following rules with Source=Anywhere: ALL ICMP [For pinging], SSH, HTTP, DNS (UDP), DNS (TCP)
• Assign a permanent IP to your instance
  • To do this, click the “Elastic IPs” under “Network & Security” in the left menu.
  • Click “Allocate New Address”.
  • After creating it, right click the new address, then “Associate Address”, and assign it to your new instance.
• You should probably set this IP up as an A record somewhere. I will refer to this IP as dns.yourdomain.com from now on.
• Login to root
  • SSH into your instance as the ec2-user via “ssh ec2-user@dns.yourdomain.com”. If in windows, you could also use putty.
  • Sudo into root via “sudo su”.
• Allow root login
  • At this point, I recommend setting it up so you can directly root into the server. Warning: some people consider this a security risk.
  • Copy your key pair(s) to the root user via “cat /home/ec2-user/.ssh/authorized_keys > /root/.ssh/authorized_keys”
  • Set SSHD to permit root logins by changing the PermitRootLogin variable to “yes” in /etc/ssh/sshd_config. A quick command to do this is “perl -pi -e 's/^\s*#?\s*PermitRootLogin.*$/PermitRootLogin yes/igm' /etc/ssh/sshd_config”, and then reload the SSHD config with “service sshd reload”. Make sure to attempt to directly log into SSH as root before exiting your current session to make sure you haven’t locked yourself out.
• Install apache (the web server), bind/named (the DNS server), and PHP (a scripting language)
  • yum -y install bind httpd php
• Start and set services to run at boot
  • service httpd start; service named start; chkconfig httpd on; chkconfig named on;
• Set the DNS server to be usable by other computers
  • Edit /etc/named.conf and change the 2 following lines to have the value “any”: “listen-on port 53” and “allow-query”
  • perl -pi -e 's/^(\s*(?:listen-on port 53|allow-query)\s*{).*$/$1 any; };/igm' /etc/named.conf; service named reload;
• Point the DNS server to the blacklist files
  • This is done by adding “include "/var/named/blacklisted.conf";” to /etc/named.conf
  • echo -ne '\ninclude "/var/named/blacklisted.conf";' >> /etc/named.conf
• Create the blacklist domain list file
  • touch /var/named/blacklisted.conf
• Create the blacklist zone file
  • Put the following into /var/named/blacklisted.db . Make sure to change dns.yourdomain.com to your domain (or otherwise, “localhost”), and 1.1.1.1 to dns.yourdomain.com’s (your server’s) IP address. Make sure to keep all periods intact.

    $TTL 14400
    @       IN SOA dns.yourdomain.com. dns.yourdomain.com ( 2003052800  86400  300  604800  3600 )
    @       IN      NS   dns.yourdomain.com.
    @       IN      A    1.1.1.1
    *       IN      A    1.1.1.1

  • The first 2 lines tell the server the domains belong to it. The 3rd line sets the base blacklisted domain to your server’s IP. The 4th line sets all subdomains of the blacklisted domain to your server’s IP.
  • This can be done via (Update the first line with your values)

    YOURDOMAIN="dns.yourdomain.com"; YOURIP="1.1.1.1";
    echo -ne "\$TTL 14400\n@       IN SOA $YOURDOMAIN. $YOURDOMAIN ( 2003052800  86400  300  604800  3600 )\n@       IN      NS   $YOURDOMAIN.\n@       IN      A    $YOURIP\n*       IN      A    $YOURIP" > /var/named/blacklisted.db;

• Fix the permissions on the blacklist files
  • chgrp named /var/named/blacklisted.*; chmod 660 /var/named/blacklisted.*;
• Set the server’s domain resolution name servers
  • The server always needs to look at itself before other DNS servers. To do this, comment out everything in /etc/resolv.conf and add to it “nameserver localhost”. This is not the best solution. I’ll find something better later.
  • perl -pi -e 's/^(?!;)/;/gm' /etc/resolv.conf; echo -ne '\nnameserver localhost' >> /etc/resolv.conf
• Run a test
  • At this point, it’s a good idea to make sure the DNS server is working as intended. So first, we’ll add an example domain to the DNS server.
  • Add the following to /var/named/blacklisted.conf and restart named to get the server going with example.com: “zone "example.com" { type master; file "blacklisted.db"; };”
  • echo 'zone "example.com" { type master; file "blacklisted.db"; };' >> /var/named/blacklisted.conf; service named reload;
  • Ping “test.example.com” and make sure it’s IP is your server’s IP
  • Set your computer’s DNS to your server’s IP in your computer’s network settings, ping “test.example.com” from your computer, and make sure the returned IP is your server’s IP. If it works, you can restore your computer’s DNS settings.
• Have the server return a message when a blacklisted domain is accessed
  • Add your message to /var/www/html
  • echo 'Domain is blocked' > /var/www/html/index.html
  • Set all URL paths to show the message by adding the following to the /var/www/html/.htaccess file

    RewriteEngine on
    RewriteCond %{REQUEST_URI} !index.html
    RewriteCond %{REQUEST_URI} !AddRules/
    RewriteRule ^(.*)$ /index.html [L]

  • echo -ne 'RewriteEngine on\nRewriteCond %{REQUEST_URI} !index.html\nRewriteCond %{REQUEST_URI} !AddRules/\nRewriteRule ^(.*)$ /index.html [L]' > /var/www/html/.htaccess
  • Turn on AllowOverride in the /etc/httpd/conf/httpd.conf for the document directory (/var/www/html/) via “ perl -0777 -pi -e 's~(<Directory "/var/www/html">.*?\n\s*AllowOverride).*?\n~$1 All~s' /etc/httpd/conf/httpd.conf”
  • Start the server via “service httpd graceful”
• Create a script that allows apache to refresh the name server’s settings
  • Create a script at /var/www/html/AddRules/restart_named with “/sbin/service named reload” and set it to executable
  • mkdir /var/www/html/AddRules; echo '/sbin/service named reload' > /var/www/html/AddRules/restart_named; chmod 755 /var/www/html/AddRules/restart_named
  • Allow the user to run the script as root by adding to /etc/sudoers “apache ALL=(root) NOPASSWD: /var/www/html/AddRules/restart_named” and “Defaults!/var/www/html/AddRules/restart_named !requiretty”
  • echo -e 'apache ALL=(root) NOPASSWD:/var/www/html/AddRules/restart_named\nDefaults!/var/www/html/AddRules/restart_named !requiretty' >> /etc/sudoers
• Create a script that allows the user to add, remove, and list the blacklisted domains
  • Add the following to /var/www/html/AddRules/index.php (one line command not given. You can use “nano” to create it)

    <?php
    //Get old domains
    $BlockedFile='/var/named/blacklisted.conf';
    $CurrentZones=Array();
    foreach(explode("\n", file_get_contents($BlockedFile)) as $Line)
            if(preg_match('/^zone "([\w\._-]+)"/', $Line, $Results))
                    $CurrentZones[]=$Results[1];
    
    //List domains
    if(isset($_REQUEST['List']))
            return print implode('<br>', $CurrentZones);
    
    //Get new domains
    if(!isset($_REQUEST['Domains']))
            return print 'Missing Domains';
    $Domains=$_REQUEST['Domains'];
    if(!preg_match('/^[\w\._-]+(,[\w\._-]+)*$/uD', $Domains))
            return print 'Invalid domains string';
    $Domains=explode(',', $Domains);
    
    //Remove domains
    if(isset($_REQUEST['Remove']))
    {
            $CurrentZones=array_flip($CurrentZones);
            foreach($Domains as $Domain)
                    unset($CurrentZones[$Domain]);
            $FinalDomainList=array_keys($CurrentZones);
    }
    else //Combine domains
            $FinalDomainList=array_unique(array_merge($Domains, $CurrentZones));
    
    //Output to the file
    $FinalDomainData=Array();
    foreach($FinalDomainList as $Domain)
            $FinalDomainData[]=
                    "zone \"$Domain\" { type master; file \"blacklisted.db\"; };";
    file_put_contents($BlockedFile, implode("\n", $FinalDomainData));
    
    //Reload named
    print `sudo /var/www/html/AddRules/restart_named`;
    ?>

  • Add the “apache” user to the “named” group so the script can update the list of domains in /var/named/blacklisted.conf via “usermod -a -G named apache; service httpd graceful;”
• Run the domain update script
  • To add a domain (separate by commas): http://dns.yourdomain.com/AddRules/?Domains=domain1.com,domain2.com
  • To remove a domain (add “Remove&” after the “?”): http://dns.yourdomain.com/AddRules/?Remove&Domains=domain1.com,domain2.com
  • To list the domains: http://dns.yourdomain.com/AddRules/?List
• Password protect the domain update script
  • Add to AddRules/.htaccess the following

    AuthType Basic
    AuthName "Admins Only"
    AuthUserFile "/var/www/html/AddRules/.htpasswd"
    require valid-user

  • echo -ne 'AuthType Basic\nAuthName "Admins Only"\nAuthUserFile "/var/www/html/AddRules/.htpasswd"\nrequire valid-user' > /var/www/html/AddRules/.htaccess
  • Warning: Putting the password file in an http accessible directory is a security risk. I just did this for sake of organization.
  • Create the user+password via “htpasswd -bc /var/www/html/AddRules/.htpasswd USERNAME” and then entering the password

To permanently set “localhost” as the resolver DNS, add “DNS1=localhost” to “/etc/sysconfig/network-scripts/ifcfg-eth0”. I have not yet confirmed this edit.

Soon after setting up this DNS server, it started getting hit by a DNS amplification attack. As the server is being used as a client’s DNS server, turning off recursion is not available. The best solution is to limit the people who can query the name server via an access list (usually a specific subnet), but that would very often not be an option either. The solution I currently have in place, which I have not actually verified if it works, is to add a forced-forward rule which only makes external requests to the name server provided by Amazon. To do this, get the name server’s IP from /etc/resolv.conf (it should be commented from an earlier step). Then add the following to your named.conf in the “options” section.

	forwarders {
		DNS_SERVER_IP;
	};
	forward only;

After I added this rule, external DNS requests stopped going through completely. To fix this, I turned “dnssec-validation” to “no” in the named.conf. Don’t forget to restart the service once you have made your changes.

#User defined variables
VARIABLES_SET=0; #Set this to 1 to allow the script to run
YOUR_DOMAIN="localhost";
YOUR_IP="1.1.1.1";
BLOCKED_ERROR_MESSAGE="Domain is blocked";
ADDRULES_USERNAME="YourUserName";
ADDRULES_PASSWORD="YourPassword";

#Confirm script is ready to run
if [ $VARIABLES_SET != 1 ]; then
    echo 'Variables need to be set in the script';
    exit 1;
fi
if [ `whoami` != 'root' ]; then
    echo 'Must be root to run script. When running the script, add "sudo" before it to' \
        'run as root';
    exit 1;
fi

#Allow root login
cat /home/ec2-user/.ssh/authorized_keys > /root/.ssh/authorized_keys;
perl -pi -e 's/^\s*#?\s*PermitRootLogin.*$/PermitRootLogin yes/igm' /etc/ssh/sshd_config;
service sshd reload;

#Install services
yum -y install bind httpd php;
chkconfig httpd on;
chkconfig named on;
service httpd start;
service named start;

#Set the DNS server to be usable by other computers
perl -pi -e 's/^(\s*(?:listen-on port 53|allow-query)\s*{).*$/$1 any; };/igm' \
    /etc/named.conf;
service named reload;

#Create/link the blacklist files
echo -ne '\ninclude "/var/named/blacklisted.conf";' >> /etc/named.conf;
touch /var/named/blacklisted.conf;

#Create the blacklist zone file
echo -ne "\$TTL 14400
@       IN SOA $YOUR_DOMAIN. $YOUR_DOMAIN ( 2003052800  86400  300  604800  3600 )
@       IN      NS   $YOUR_DOMAIN.
@       IN      A    $YOUR_IP
*       IN      A    $YOUR_IP" > /var/named/blacklisted.db;

#Fix the permissions on the blacklist files
chgrp named /var/named/blacklisted.*;
chmod 660 /var/named/blacklisted.*;

#Set the server’s domain resolution name servers
perl -pi -e 's/^(?!;)/;/gm' /etc/resolv.conf;
echo -ne '\nnameserver localhost' >> /etc/resolv.conf;

#Run a test
echo 'zone "example.com" { type master; file "blacklisted.db"; };' >> \
    /var/named/blacklisted.conf;
service named reload;
FOUND_IP=`dig -t A example.com | grep -ioP "^example\.com\..*?"'in\s+a\s+[\d\.:]+' | \
     grep -oP '[\d\.:]+$'`;
if [ "$YOUR_IP" == "$FOUND_IP" ]
then
  echo 'Success: Example domain matches your given IP' > /dev/stderr;
else
  echo 'Warning: Example domain does not match your given IP' > /dev/stderr;
fi

#Have the server return a message when a blacklisted domain is accessed
echo "$BLOCKED_ERROR_MESSAGE" > /var/www/html/index.html;
perl -0777 -pi -e 's~(<Directory "/var/www/html">.*?\n\s*AllowOverride).*?\n~$1 All~s' \
     /etc/httpd/conf/httpd.conf;
echo -n 'RewriteEngine on
RewriteCond %{REQUEST_URI} !index.html
RewriteCond %{REQUEST_URI} !AddRules/
RewriteRule ^(.*)$ /index.html [L]' > /var/www/html/.htaccess;
service httpd graceful;

#Create a script that allows apache to refresh the name server’s settings
mkdir /var/www/html/AddRules;
echo '/sbin/service named reload' > /var/www/html/AddRules/restart_named;
chmod 755 /var/www/html/AddRules/restart_named;

echo 'apache ALL=(root) NOPASSWD:/var/www/html/AddRules/restart_named
Defaults!/var/www/html/AddRules/restart_named !requiretty' >> /etc/sudoers;

#Create a script that allows the user to add, remove, and list the blacklisted domains
echo -n $'<?php
//Get old domains
$BlockedFile=\'/var/named/blacklisted.conf\';
$CurrentZones=Array();
foreach(explode("\\n", file_get_contents($BlockedFile)) as $Line)
        if(preg_match(\'/^zone "([\\w\\._-]+)"/\', $Line, $Results))
                $CurrentZones[]=$Results[1];

//List domains
if(isset($_REQUEST[\'List\']))
        return print implode(\'<br>\', $CurrentZones);

//Get new domains
if(!isset($_REQUEST[\'Domains\']))
        return print \'Missing Domains\';
$Domains=$_REQUEST[\'Domains\'];
if(!preg_match(\'/^[\\w\\._-]+(,[\\w\\._-]+)*$/uD\', $Domains))
        return print \'Invalid domains string\';
$Domains=explode(\',\', $Domains);

//Remove domains
if(isset($_REQUEST[\'Remove\']))
{
        $CurrentZones=array_flip($CurrentZones);
        foreach($Domains as $Domain)
                unset($CurrentZones[$Domain]);
        $FinalDomainList=array_keys($CurrentZones);
}
else //Combine domains
        $FinalDomainList=array_unique(array_merge($Domains, $CurrentZones));

//Output to the file
$FinalDomainData=Array();
foreach($FinalDomainList as $Domain)
    $FinalDomainData[]="zone \\"$Domain\\" { type master; file \\"blacklisted.db\\"; };";
file_put_contents($BlockedFile, implode("\\n", $FinalDomainData));

//Reload named
print `sudo /var/www/html/AddRules/restart_named`;
?>' > /var/www/html/AddRules/index.php;

usermod -a -G named apache;
service httpd graceful;

#Password protect the domain update script
echo -n 'AuthType Basic
AuthName "Admins Only"
AuthUserFile "/var/www/html/AddRules/.htpasswd"
require valid-user' > /var/www/html/AddRules/.htaccess;

htpasswd -bc /var/www/html/AddRules/.htpasswd "$ADDRULES_USERNAME" "$ADDRULES_PASSWORD";

echo 'Script complete';