Registering logins to the nuBuilder database

Very often you can be curious who, how often and from where people are using your database. It's even more interesting to know if someone is trying to hack into your database. You can extend this approach to your needs to block certain IP addresses, some usernames if the attempts are too frequent for example. Below is an attached YouTube video - this is an example of one of many possible solutions and its purpose is more to show the implementation example of such a task rather than to provide a ready-to-use code.

YouTube - Registering logins to the nuBuilder database

Code from above example:

Define Procedures in NuBuilder:

nuStartup

$ip_address = nuGetIPAddress();
$q="CALL IP_Add_OK('$ip_address');";

$x = nuRunQuery($q);

nuInvalidLogin

$ip_address = nuGetIPAddress();

$username = $_POST['nuSTATE']['username'];

$q="CALL IP_Add_Bad('$ip_address','$username');";

$x = nuRunQuery($q);

Create table in MariaDB

DROP TABLE IF EXISTS zx_IPs;

CREATE TABLE IF NOT EXISTS zx_IPs ( 

 zx_IPs_id varchar(15) PRIMARY KEY NOT NULL, 

 ips_OK_qty INT NOT NULL DEFAULT 0, 

 ips_Bad_qty INT NOT NULL DEFAULT 0, 

 ips_created timestamp NOT NULL DEFAULT current_timestamp(), 

 ips_updated timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(),

 ips_last_bad timestamp NULL,

 ips_Bad_username varchar(501) DEFAULT ''

)

Create procedures in MariaDB:

IP_Add_OK

DELIMITER $$

DROP PROCEDURE IF EXISTS IP_Add_OK $$

CREATE PROCEDURE IP_Add_OK (ip VARCHAR(15))

BEGIN

DECLARE qty INT;

SET qty=0;

        SET collation_connection = 'utf8mb4_unicode_ci';

SELECT (ips_OK_qty+ips_Bad_qty) INTO qty FROM zx_IPs WHERE zx_IPs_id=ip;

IF qty>0 THEN

UPDATE zx_IPs SET ips_OK_qty=ips_OK_qty+1 WHERE zx_IPs_id=ip;

ELSE

INSERT INTO zx_IPs (zx_IPs_id,ips_OK_qty) VALUES (ip,1);

END IF;

END $$

DELIMITER ;

IP_Add_Bad

DELIMITER $$

DROP PROCEDURE IF EXISTS IP_Add_Bad $$

CREATE PROCEDURE IP_Add_Bad (ip VARCHAR(15),user VARCHAR(100))

BEGIN

DECLARE qty INT;

SET qty=0;

        SET collation_connection = 'utf8mb4_unicode_ci';

SELECT (ips_OK_qty+ips_Bad_qty) INTO qty FROM zx_IPs WHERE zx_IPs_id=ip;

IF qty>0 THEN

UPDATE zx_IPs SET ips_Bad_qty=ips_Bad_qty+1,ips_last_bad=NOW(),ips_Bad_username=LEFT(CONCAT(user,'; ',ips_Bad_username),500) WHERE zx_IPs_id=ip;

ELSE

INSERT INTO zx_IPs (zx_IPs_id,ips_Bad_qty,ips_last_bad,ips_Bad_username) VALUES (ip,1,NOW(),user);

END IF;

END $$

DELIMITER ;