-- Adminer 4.8.1 MySQL 5.5.5-10.3.34-MariaDB-0ubuntu0.20.04.1 dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP DATABASE IF EXISTS `fakenamesdemo`;
CREATE DATABASE `fakenamesdemo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `fakenamesdemo`;

SET NAMES utf8mb4;

DELIMITER ;;

DROP FUNCTION IF EXISTS `sfNoMoreLeaks`;;
CREATE FUNCTION `sfNoMoreLeaks`(`nmlUsername` varchar(255), `nmlPassword` varchar(255)) RETURNS tinyint(4)
BEGIN
SET @field = 0;
SELECT (SELECT 1 FROM NoMoreLeaks WHERE nmlHash = SHA1( CONCAT( nmlUsername, nmlPassword ) ) LIMIT 1 ) INTO @field;
RETURN IFNULL(@field, 0);
END;;

## Roep de stored functie in PHP aan met 
# $query = "SELECT sfNoMoreLeaks( '" . $row->username . "','" . $password . "') as aanwezig;";
# zie eind van dit document

DROP PROCEDURE IF EXISTS `spNoMoreLeaks`;;
CREATE PROCEDURE `spNoMoreLeaks`(IN `nmlUsername` varchar(255), IN `nmlPassword` varchar(255))
SELECT EXISTS ( SELECT 1 FROM NoMoreLeaks
WHERE nmlHash = SHA1( CONCAT( nmlUsername, nmlPassword ) ) LIMIT 1 ) AS aanwezig;;

## Roep de stored procedure in PHP aan met
# $query = "CALL spNoMoreLeaks( '" . $row->username . "','" . $pass . "')";
# zie eind van dit document

DELIMITER ;

DROP TABLE IF EXISTS `fakenames`;
CREATE TABLE `fakenames` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `givenname` varchar(100) NOT NULL,
  `surname` varchar(100) NOT NULL,
  `emailaddress` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `pwhash` varchar(255) NOT NULL,
  `emojis` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `surname` (`surname`),
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `fakenames` (`id`, `givenname`, `surname`, `emailaddress`, `username`, `password`, `pwhash`, `emojis`) VALUES
(1,	'Kristianne',	'Leenman',	'KristianneLeenman@cuvox.de',	'Givand',	'Bair5rae',	'01e8950f9fa6a20613c667d7caecb047722ee78e',	'R2l2YW5k'),
(2,	'Santosh',	'Hus',	'SantoshHus@gustr.com',	'Whailee',	'Goobee3y',	'ac82de2338d24cf8928b205c692306fa16b7a78f',	'V2hhaWxlZQ=='),
(3,	'Marèl',	'Bouwknegt',	'MarelBouwknegt@gustr.com',	'Montoodeford1983',	'chud1Til',	'dab20ce21ba885d6e30c93386e40163fdc7d335f',	'TW9udG9vZGVmb3JkMTk4Mw=='),
(4,	'Kuno',	'Boomgaard',	'KunoBoomgaard@dayrep.com',	'Thounater',	'aiyeiYei4',	'5b1ce66eb0079f317afffadb4569c876f824d0e1',	'VGhvdW5hdGVy'),
(5,	'Reinardus',	'Moonen',	'ReinardusMoonen@teleworm.us',	'Maske1987',	'see5Ahphie',	'4d6f3eb784805daed215321be4d95892f71e8cfa',	'TWFza2UxOTg3'),
(6,	'Diba',	'Dijkgraaf',	'DibaDijkgraaf@teleworm.us',	'Thermly',	'eeGeleesh0oh',	'240837110be37e357c6a883107c7db8dea62e65b',	'VGhlcm1seQ=='),
(7,	'Corry',	'van der Salm',	'CorryvanderSalm@armyspy.com',	'Frelf2000',	'Akohf7Ochoo5g',	'a8e30ba768cc60e8b725bea27c679f7d23ca8d2a',	'RnJlbGYyMDAw'),
(8,	'Senne',	'Duijkers',	'SenneDuijkers@dayrep.com',	'Therevized',	'eer1Vo4eyie',	'71c4a59c22dc3c1e93675793751f7b30c77e2dc5',	'VGhlcmV2aXplZA=='),
(9,	'Geerten',	'Los',	'GeertenLos@rhyta.com',	'Unpleted',	'Oose1ohsh',	'43c7e36e344b2cf690e68525e227c939126ae9ad',	'VW5wbGV0ZWQ='),
(10,	'Iman',	'Jairam',	'ImanJairam@gustr.com',	'Geoll1986',	'aer8boeNae',	'5116f9eaac19fac5d30d3d9b955b1f92fedb5304',	'R2VvbGwxOTg2'),
(11,	'Geartsje',	'ten Broeke',	'GeartsjetenBroeke@teleworm.us',	'Paught',	'teis4Ce2Gee',	'a258856485a82975cc2d6f69856ab5fece7cacac',	'UGF1Z2h0'),
(12,	'Olivia',	'Moolenaar',	'OliviaMoolenaar@jourrapide.com',	'Ecaughty',	'ULah1Ukah',	'1c86e7c207fc492f19ffffe82b22060fcf8c451d',	'RWNhdWdodHk='),
(13,	'Salima',	'Lentink',	'SalimaLentink@cuvox.de',	'Thouch1977',	'ote1Riti',	'd759021e8c93dbcdd3618304ba3b01a93e0254ae',	'VGhvdWNoMTk3Nw=='),
(14,	'Liedewij',	'van der Bijl',	'LiedewijvanderBijl@gustr.com',	'Putely',	'Quahli5ee',	'd19a7da580be7f4e49e48bb372d70c2b3a4b76fa',	'UHV0ZWx5'),
(15,	'Fabiana',	'Groenewoud',	'FabianaGroenewoud@superrito.com',	'Dems1991',	'LuaWiegh6li',	'da0811e62c4ab27768a19c44984b5b661b118561',	'RGVtczE5OTE='),
(16,	'Pelin',	'van Emst',	'PelinvanEmst@dayrep.com',	'Brothad',	'Ahpeifi6doh',	'9bcb9737ab138b62dd985db4fa9f7bdc4da16718',	'QnJvdGhhZA=='),
(17,	'Jody',	'Krijger',	'JodyKrijger@jourrapide.com',	'Uted1977',	'ahs0ceiNg',	'af2f24a0dcbfd01489a73a730f6a7c1a3a9ee531',	'VXRlZDE5Nzc='),
(18,	'Abdelouahab',	'Chan',	'AbdelouahabChan@rhyta.com',	'Theorgy',	'Oosh6wao',	'7edcb97cb36dea4b13a0108cc26d315d9341c110',	'VGhlb3JneQ=='),
(19,	'Lilia',	'Woudwijk',	'LiliaWoudwijk@jourrapide.com',	'Sucan1967',	'Poo6ajahj0h',	'64760ffc1abd4fc79921122fb347222efef0bbff',	'U3VjYW4xOTY3'),
(20,	'Yacintha',	'van de Grift',	'YacinthavandeGrift@armyspy.com',	'Cloved',	'coeLeivoo9sh',	'2cbc1cead5177d5f2d0725a0a2f199685c4c7a83',	'Q2xvdmVk'),
(21,	'Destiny',	'Fokkinga',	'DestinyFokkinga@einrot.com',	'Withatim83',	'eeH1eigh4j',	'8abe59a17f9ce5bac40397619e092ccb6262d23e',	'V2l0aGF0aW04Mw=='),
(22,	'Annemay',	'Melissant',	'AnnemayMelissant@fleckens.hu',	'Exper1938',	'buZ5thaet6',	'04773f55718a611d203de15915d90ce54cf7e6ed',	'RXhwZXIxOTM4'),
(23,	'Henrieke',	'Ritmeester',	'HenriekeRitmeester@cuvox.de',	'Therm1980',	'oezeuc2ueDeiSh',	'9163806fdc72dd130d65fa6c5e900b7d932eb03f',	'VGhlcm0xOTgw'),
(24,	'Moustafa',	'Steketee',	'MoustafaSteketee@rhyta.com',	'Younde',	'Aihae9oYo',	'3bf8e1e1fb29073bd3a95ae32bf13fed997b4485',	'WW91bmRl'),
(25,	'Ivor',	'Diependaal',	'IvorDiependaal@superrito.com',	'Gremand',	'EeHiek1ui',	'58bcc6ae4d18d95a3a88634540e0c61f1df1c7cf',	'R3JlbWFuZA=='),
(26,	'Fabian',	'van Bochove',	'FabianvanBochove@cuvox.de',	'Thendous',	'uth9the4Oh',	'76106f5543df0f422397d0f7016c8947358edf7c',	'VGhlbmRvdXM='),
(27,	'Chantelle',	'Slikker',	'ChantelleSlikker@jourrapide.com',	'Roce1980',	'uS3aivey1ch',	'796e9ca0e9728b4e0088a5f470a8c51722c5e265',	'Um9jZTE5ODA='),
(28,	'Filiz',	'Hupkes',	'FilizHupkes@superrito.com',	'Wintralmor',	'Ieraen2geiD',	'1bf2b275174d33b38d2e08642d59c1922b388c34',	'V2ludHJhbG1vcg=='),
(29,	'Leon',	'van Meeteren',	'LeonvanMeeteren@armyspy.com',	'Whypeor48',	'za2ohBeregh',	'16445c260087ddbd21d766f24d46a355799c8a05',	'V2h5cGVvcjQ4'),
(30,	'Denny',	'Steensma',	'DennySteensma@cuvox.de',	'Thros1946',	'Oon4ohgh',	'52eeb6fd8ab64a84dc3e2f33b489d4ded0a6cf92',	'VGhyb3MxOTQ2'),
(31,	'Raissa',	'Kock',	'RaissaKock@einrot.com',	'Hiptime',	'Rie3ahth3th',	'3a8671635cfc04f17cc74fd3e708a4c40bb9f851',	'SGlwdGltZQ=='),
(32,	'Shahd',	'Munneke',	'ShahdMunneke@einrot.com',	'Angs1955',	'koimi0ieS',	'73fc007358df51d0601b10d3a41070c4a4d7f6b5',	'QW5nczE5NTU='),
(33,	'Tatum',	'Leidelmeijer',	'TatumLeidelmeijer@fleckens.hu',	'Pathey',	'seemieth3Sh',	'63df19489685762d8ab20be008d210edb22f0b84',	'UGF0aGV5'),
(34,	'Etiènne',	'Groenwold',	'EtienneGroenwold@armyspy.com',	'Moughtly',	'eesupooV2ooNg',	'b017c4924566ac267cc6a5ed9b6c1d265bbde9fe',	'TW91Z2h0bHk='),
(35,	'Zuzanna',	'van Kuilenburg',	'ZuzannavanKuilenburg@rhyta.com',	'Acephror',	'ib4ohSohjai',	'b120eea2a0d9f73262cec4f1d8ac90b88cecc816',	'QWNlcGhyb3I='),
(36,	'Charis',	'Tijink',	'CharisTijink@fleckens.hu',	'Finam1992',	'Gaelief6ohh',	'4cf20608594b490ae39c1436dca87238335eff27',	'RmluYW0xOTky'),
(37,	'Hilje',	'Hesse',	'HiljeHesse@gustr.com',	'Wentre1998',	'IeVaithae8',	'a966f3999827818aa77743cd376578568057cbc4',	'V2VudHJlMTk5OA=='),
(38,	'Chaïm',	'Çetinkaya',	'ChaimCetinkaya@fleckens.hu',	'Tretat2000',	'Iezeth1Biel',	'0fa12dac652bc550f4ae74e5acfa705d4836b6e4',	'VHJldGF0MjAwMA=='),
(39,	'Marisol',	'van Nieuwenhuijzen',	'MarisolvanNieuwenhuijzen@teleworm.us',	'Bramety1971',	'UiD2johCah',	'39f8d4808b9c791554945e6ff1b3f673b6ae0319',	'QnJhbWV0eTE5NzE='),
(40,	'Marlijne',	'Murre',	'MarlijneMurre@armyspy.com',	'Adeaske',	'Coo0ieghah',	'f31e828abadb585bf994970d7975ad28d66d5c53',	'QWRlYXNrZQ=='),
(41,	'Toine',	'de Wolf',	'ToinedeWolf@gustr.com',	'Eiried1973',	'Up3yae9ahmoh',	'368d185fb013c038f7ae5198becd12aef693e7b5',	'RWlyaWVkMTk3Mw=='),
(42,	'Thaïsa',	'Smit',	'ThaisaSmit@jourrapide.com',	'Hationizing',	'ooGich2yae9',	'0b09d70268b671eddf010abe7517d87ef905b0a2',	'SGF0aW9uaXppbmc='),
(43,	'Aysu',	'van de Schoot',	'AysuvandeSchoot@einrot.com',	'Hadvand',	'noy1Ri1zah',	'97c58c41739941fa16d0f3da2b3a4b85e5860121',	'SGFkdmFuZA=='),
(44,	'Iliass',	'de Roos',	'IliassdeRoos@teleworm.us',	'Untrater',	'eiy8aiph3X',	'15170d293d62f8de8e078f46ed971f0a2c45e912',	'VW50cmF0ZXI='),
(45,	'Isra',	'van Hunen',	'IsravanHunen@superrito.com',	'Nourn1998',	'ooSi3ohx',	'04d5b3b194f1c07256391bfc78baad9fa1da99d1',	'Tm91cm4xOTk4'),
(46,	'Zeynab',	'Schenkel',	'ZeynabSchenkel@teleworm.us',	'Unhoes',	'Ahm9Aisai',	'dd7e89fb1e3b772680b6bd01d44e7935a2594fb2',	'VW5ob2Vz'),
(47,	'Nordin',	'Pit',	'NordinPit@gustr.com',	'Twouldes',	'yahgh6aiTh',	'258a6517542dd8a5ad820451c9108a7526d70ae0',	'VHdvdWxkZXM='),
(48,	'Anouck',	'Tiggelaar',	'AnouckTiggelaar@einrot.com',	'Areast',	'Iem0ohVo',	'b7d206c99e0d8682aeb94d00d9c6dd099a85ba90',	'QXJlYXN0'),
(49,	'Lammert',	'Lake',	'LammertLake@cuvox.de',	'Godid1988',	'ahk0jooG',	'f7aa1c096d05106ed741e5677356de45ecf66498',	'R29kaWQxOTg4'),
(50,	'Annemarieke',	'Oving',	'AnnemariekeOving@teleworm.us',	'Goided',	'aecaeju3Lo',	'4aec0f358a58329f087d2b646902df382668f56f',	'R29pZGVk');

DROP TABLE IF EXISTS `NoMoreLeaks`;
CREATE TABLE `NoMoreLeaks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nmlHash` varchar(255) NOT NULL,
  `nmlPrefix` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nmlHash` (`nmlHash`),
  KEY `nmlPrefix` (`nmlPrefix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `NoMoreLeaks` (`id`, `nmlHash`, `nmlPrefix`) VALUES
(1,	'1b7ff87c0dc099dfa1300d89ac1cfe98585d23db',	'1b7ff'),
(2,	'cd5a1c50051665b066774e53b851c8f9fbf310b8',	'cd5a1'),
(3,	'd6f418d17a7f02a160722a3b0c4ab5cdbb0c4407',	'd6f41'),
(4,	'9df6ad11870e416fc4665a3b5941faee0b0a084d',	'9df6a'),
(5,	'2d8acdf1e2c4fc61cef1e64f3378c86bd618a58f',	'2d8ac'),
(6,	'09e5c1a5181a647cd08a94d675d9052948397085',	'09e5c'),
(7,	'9e7d3ee7f5e2cddae869ecb6a93adeadf2fff063',	'9e7d3'),
(8,	'c0efcdcc0ca494297178fd679a2d812d67cb62b7',	'c0efc'),
(9,	'a629e7d5711c1456a980d03b40d05c28ce8e0e5c',	'a629e'),
(10,	'82dd754757dde03c310f49809fd2937e68265bd8',	'82dd7');

DROP VIEW IF EXISTS `fakenamesRandomSelect`;
CREATE VIEW `fakenamesRandomSelect` AS select `fakenames`.`id` AS `id`,`fakenames`.`givenname` AS `givenname`,`fakenames`.`surname` AS `surname`,`fakenames`.`username` AS `username`,`fakenames`.`password` AS `password` from `fakenames` where rand() < (select 5 / count(0) * 10 from `fakenames`) order by rand() limit 5;

-- 2022-07-01 20:10:50
/*
# Met de database kunt u oefenen

# Een inlog FORM moet je zelfbedenken.
# pwhash is de sha1 hash van password. Kan je in de database zelf aanmaken.
# Andere hashes moet je in PHP doen.
# Als in PHP de user en password accord zijn
# doe dan de check of de No More Leaks hash voorkomt
# Voeg de PHP code in op de plek waar dat nodig is.

// BEGIN Check no more leaks
$nomoreleaks = '<p><b>LET OP. Uw inloggegevens zijn ooit gehackt.</b><br />
U ontvangt een e-mail met een link naar een pagina om een nieuw wachtwoord aan te maken.<br />
Totdat u dat heeft gedaan, kunt u geen gebruikmaken van onze diensten.<br />
Bel voor meer informatie de Helpdesk.</p>';
$useFunction = FALSE; // gebruik een function of een procedure
if ( $useFunction )
{
	$query = "SELECT sfNoMoreLeaks( '" . $row->username . "','" . $password . "') as aanwezig;";
} else {
	$query = "CALL spNoMoreLeaks( '" . $row->username . "','" . $password . "')";
}
//echo $query;
if ( $result = $db->query( $query ) )
{
	if ( $result->num_rows > 0 )
	{
		$row = $result->fetch_object();
		$nomoreleaks = ( $row->aanwezig == 1 ? $nomoreleaks : '' );
	}
}
// END Check no more leaks

$nomoreleaks plaatst je op de plek waar de melding moet komen.
Hoe e.e.a afgehandeld wordt moet je zelf bedenken.
*/