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); }