public bool IsAuthorized(string username, string password)
{
string uName = string.Empty;
string uPass = string.Empty;
string uSt = string.Empty;
int uID;
if (!string.IsNullOrEmpty(username))
{
username = username.ToLower();
}
var lastaccess = DateTime.UtcNow;
const string strSQLQuery = @"
SELECT id, username, password, status
FROM swagger
WHERE lower(username) = @username;
UPDATE swagger SET lastaccess = @lastaccess, lastipaddr = @ipaddr WHERE lower(username) = @username;";
try
{
using (var connection = new System.Data.SQLite.SQLiteConnection(VarsSubsFunc.mStrSQLiteConnString))
{
using (var command = new System.Data.SQLite.SQLiteCommand(strSQLQuery, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@username", username));
command.Parameters.Add("@lastaccess", DbType.DateTime);
command.Parameters["@lastaccess"].Value = lastaccess;
command.Parameters.Add("@ipaddr", DbType.String);
command.Parameters["@ipaddr"].Value = VarsSubsFunc.GetIpAddress().Trim();
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
uID = Conversions.ToInteger(reader[0]);
uName = reader[1].ToString();
uPass = reader[2].ToString();
uSt = reader[3].ToString();
}
}
}
}
}
if (uName is null || string.IsNullOrEmpty(uName) || (uName ?? "") == (string.Empty ?? ""))
{
return(false);
}
if (uSt is null || uSt == "0" || (uSt ?? "") == (string.Empty ?? ""))
{
return(false);
}
if (!SimpleHash.VerifyHash(password, "SHA256", uPass))
{
return(false);
}
return(true);
}
catch (Exception)
{
return(false);
}
// Return username.Equals("admin", StringComparison.InvariantCultureIgnoreCase) AndAlso password.Equals("123456")
}
public static bool Create_db()
{
bool bolR;
var con = new System.Data.SQLite.SQLiteConnection();
var cmd = new System.Data.SQLite.SQLiteCommand();
string str_sql;
bolR = true;
Directory.CreateDirectory(mPathWEBAPI + "Data");
if (!File.Exists(mStrSQLiteDBFile))
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(mStrSQLiteDBFile);
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
// con.ChangePassword(mStrDBPassword)
cmd.Connection = con;
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(@"
CREATE TABLE IF NOT EXISTS [users] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[name] VARCHAR(512) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[email] VARCHAR(512) DEFAULT (null),
[role] VARCHAR(512) DEFAULT (null),
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'users';
CREATE UNIQUE INDEX [id]
ON [users] (
[id] ASC
);
INSERT INTO users (username, name, password, role) VALUES ('admin', 'Administrator', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', 'Administrators');
INSERT INTO users (username, name, password, email, role) VALUES ('robs', 'Roberto Gaxiola', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', '*****@*****.**', 'Administrators');
CREATE TABLE IF NOT EXISTS [tokens] (
[id] INTEGER NOT NULL DEFAULT 1 PRIMARY KEY AUTOINCREMENT,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[userid] INTEGER NOT NULL,
[refresh_token] VARCHAR(1024) NOT NULL,
[status] INTEGER NOT NULL DEFAULT(1),
[ipaddr] VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
WriteActivityLog(ex.Message, 2);
return(false);
}
finally
{
con.Close();
}
}
try
{
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
cmd.Connection = con;
var dtB = con.GetSchema("Columns");
if (dtB.Select("COLUMN_NAME = 'ipaddr' AND TABLE_NAME = 'tokens'").Length == 0)
{
str_sql = "ALTER TABLE tokens ADD COLUMN [ipaddr] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("COLUMN_NAME = 'name' AND TABLE_NAME = 'users'").Length == 0)
{
str_sql = "ALTER TABLE users ADD COLUMN [name] VARCHAR(512);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'validations'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [validations] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("COLUMN_NAME = 'method' AND TABLE_NAME = 'validations'").Length == 0)
{
str_sql = "ALTER TABLE validations ADD COLUMN [method] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"CREATE TABLE IF NOT EXISTS [validationsbk] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"INSERT INTO validationsbk
SELECT id,date,requestUri,method,status,statusMsg,ipaddr,userid,username,role,email,nbf_date,iat_date,exp_date,nbf,iat,exp,iss,aud,jti,token
FROM validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"DROP table validations;
ALTER TABLE validationsbk RENAME TO validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'swagger'").Length == 0)
{
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(@"CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("TABLE_NAME = 'cardex_swagger'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [cardex_swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[status] INTEGER,
[statusMsg] TEXT,
[username] VARCHAR(50),
[ipaddr] VARCHAR(20)
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'params'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [params] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[swagAuth] INTEGER DEFAULT (0)
);
INSERT INTO params (swagAuth) VALUES (0);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
con.Close();
}
catch (Exception)
{
}
// If mBolAuto = False Then MsgBox("Error durante actualizacion de tablas" & vbCrLf & str_sql & vbCrLf & ex.Message)
finally
{
con.Close();
}
return(bolR);
}
