[MySQL/MariaDB] HIBP Passwords Importer

README.md

Importer for HIBP Passwords (API)

Data source

https://haveibeenpwned.com/Passwords

Original posts

• https://forum.netcup.de/sonstiges/smalltalk/p144786-das-l%C3%A4ngste-thema/#post144786
• https://forum.netcup.de/sonstiges/smalltalk/p111901-das-l%C3%A4ngste-thema/#post111901
• https://forum.netcup.de/sonstiges/smalltalk/p111022-das-l%C3%A4ngste-thema/#post111022

insertFromAPI.php

#!/usr/bin/env php
<?php

function exception_error_handler($severity, $message, $file, $line)
{
	if (!(error_reporting() & $severity))
	{
		return;
	}

	throw new ErrorException($message, 0, $severity, $file, $line);
}

error_reporting(-1);
set_error_handler('exception_error_handler');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

ini_set('user_agent', 'HIPB Passwords Importer for MySQL');
$db = new mysqli('127.0.0.1', '<USERNAME>', '<PASSWORD>', '<DB>', 3306);
$stmt = $db->prepare('INSERT INTO `passwords` (`hash`) SELECT UNHEX(?) FROM DUAL WHERE (SELECT 1 FROM `passwords` WHERE `hash` = UNHEX(?)) IS NULL');
$hash = null; $stmt->bind_param('ss', $hash, $hash); $db->autocommit(false);

$init = ($argc > 1 ) ? strtolower($argv[1]) : '00000';
$init = substr($init, (substr($init, 0, 2) === '0x') * 2, 5);
$init .= ($_ = 5 - strlen($init)) ? str_repeat('0', $_) : '';

if(!preg_match('/^[0-9a-f]{5}$/', $init))
{
	throw new RuntimeException('Invalid start range');
}

$i = 0;
$l = pow(16, 5);
$f = hexdec($init);
while($f < $l)
{
	$range = sprintf('%05x', $f);
	
	$t = 0;
	while(++$t)
	{
		try
		{
			if($t > 3)
			{
				exit(1);
			}
			else if($t > 1)
			{
				sleep(90);
			}

			$url = 'https://api.pwnedpasswords.com/range/' . $range;

			if(($content = file_get_contents($url)) === false)
			{
				throw new RuntimeException('Download failed: ' . $url);
			}

			break;
		}
		catch(Throwable $e)
		{
			print("\n\n");
			print($e);
			print("\n\n");
		}
	}

	foreach(explode("\n", $content) as $line)
	{
		if(($line = trim($line)) === '')
		{
			continue;
		}
		else if(($p = strpos($line, ':')) === false)
		{
			throw new RuntimeException('Invalid line');
		}
		else if(strlen($hash = $range . substr($line, 0, $p)) != 40)
		{
			throw new RuntimeException('Invalid hash length');
		}

		$stmt->execute();
		$i++;
	}

	$f++;
	$db->commit();
	printf("\r[%.5f%%] File %d/%d (0x%s) -- %d elements processed ", 100 / ($l / $f), $f, $l, $range, $i);
}

$stmt->close();
$db->commit();
$db->close();
print("\n");

?>

table.sql

CREATE TABLE `passwords` (`hash` binary(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin COMMENT='v8' ROW_FORMAT=COMPRESSED;
ALTER TABLE `passwords` ADD KEY `5b` (`hash`(5));