private AuthenticationResult Authenticate(string id, string key)
{
AuthenticationResult result = null;
//Support checking only the lower 26 bits of the key, because of stupid Wiegand protocol!
if (key.StartsWith("W26#"))
{
using (var db = new AccessControlDatabase()) {
var sql = @"
SELECT
m.member_id AS 'Id',
m.name AS 'Name',
m.type AS 'Type',
m.apricot_admin AS Admin,
m.joined AS Joined,
m.expires AS Expiration,
DATE_ADD(m.expires, INTERVAL 7 DAY) > NOW() AS AccessGranted
FROM
member m
INNER JOIN keycode k
ON m.member_id = k.member_id
WHERE
0x00FFFFFE & CONV(k.keycode_id, 16, 10) = CONV(@0, 16, 10)
LIMIT 1;" ;
result = db.SingleOrDefault <AuthenticationResult>(sql, key.Substring(6));
}
}
else
{
using (var db = new AccessControlDatabase()) {
var sql = @"
SELECT
m.member_id AS 'Id',
m.name AS 'Name',
m.type AS 'Type',
m.apricot_admin AS Admin,
m.joined AS Joined,
m.expires AS Expiration,
DATE_ADD(m.expires, INTERVAL 7 DAY) > NOW() AS AccessGranted
FROM
member m
INNER JOIN keycode k
ON m.member_id = k.member_id
WHERE
k.keycode_id = @1
LIMIT 1;" ;
result = db.SingleOrDefault <AuthenticationResult>(sql, id, key);
}
}
RecordAttempt(id, key, result?.Id ?? -1, result?.AccessGranted ?? false, true, false);
if (result == null)
{
throw new Exception("Code not found");
}
return(result);
}
private void RecordAttempt(int readerId, string key, AuthenticationResult credentials, bool login, bool logout, string action)
{
using (var db = new AccessControlDatabase()) {
db.Execute(@"
INSERT INTO
attempt (
reader_id,
keycode,
member_id,
access_granted,
login,
logout,
action,
attempt_time
)
VALUES (
@0,
@1,
@2,
@3,
@4,
@5,
@6,
NOW()
);" ,
readerId,
key,
credentials?.Id ?? -1,
credentials?.AccessGranted ?? false,
login,
logout,
action);
}
}
private void RecordAttempt(string readerId, string key, int memberId, bool granted, bool login, bool logout)
{
using (var db = new AccessControlDatabase()) {
db.Execute(@"
INSERT INTO
attempt (
reader_id,
keycode,
member_id,
access_granted,
login,
logout,
attempt_time
)
VALUES (
@0,
@1,
@2,
@3,
@4,
@5,
NOW()
);" ,
readerId,
key,
memberId,
granted,
login,
logout);
}
}
public IActionResult Database()
{
var tmpFile = Path.GetTempPath() + "snapshot.sqlite3";
System.IO.File.Delete(tmpFile);
var snapshot = new SQLiteConnection(tmpFile, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite, false);
snapshot.BeginTransaction();
snapshot.CreateTable <attempt>();
snapshot.CreateTable <group>();
snapshot.CreateTable <group_member>();
snapshot.CreateTable <keycode>();
snapshot.CreateTable <member>();
snapshot.CreateTable <reader>();
using (var db = new AccessControlDatabase()) {
snapshot.InsertAll(db.Query <group>());
snapshot.InsertAll(db.Query <group_member>());
snapshot.InsertAll(db.Query <member>());
snapshot.InsertAll(db.Query <reader>());
// We have to get a bit more creative with the keycodes, cleaning up the data
foreach (var code in db.Query <keycode>())
{
var key = code.keycode_id.Replace("#", "");
snapshot.Insert(new keycode {
keycode_id = key,
member_id = code.member_id,
updated = code.updated
});
// Create 2nd truncated copy for W26 crap
if (key.Length == 10)
{
key = key.Substring(4, 6);
snapshot.Insert(new keycode {
keycode_id = key,
member_id = code.member_id,
updated = code.updated
});
}
}
}
snapshot.Commit();
snapshot.Close();
var stream = new FileStream(tmpFile, FileMode.Open, FileAccess.ReadWrite);
return(File(stream, "application/vnd.sqlite3", "snapshot.sqlite3"));
}
public IActionResult Initialize([FromBody] string payload)
{
try {
var request = JsonDocument.Parse(payload).RootElement;
var id = request.GetProperty("Id").GetInt32();
var version = request.GetProperty("Version").GetString();
DbResult result;
using (var db = new AccessControlDatabase()) {
var sql = @"
SELECT
r.name,
r.timeout,
r.enabled,
g.name AS groupName,
r.settings
FROM
reader r
INNER JOIN `group` g
ON r.group_id = g.group_id
WHERE
r.reader_id = @0
LIMIT 1;" ;
result = db.SingleOrDefault <DbResult>(sql, id);
}
if (id < 1 || result == null)
{
return(StatusCode(401));
}
var clientAddress = HttpContext.Connection.RemoteIpAddress.ToString();
RecordClient(id, clientAddress, version, payload);
var output = new ReaderResult {
Name = result.name,
Timeout = result.timeout,
Enabled = result.enabled,
Group = result.groupName,
Settings = result.settings,
};
return(new JsonResult(output));
}
catch (Exception ex) {
Console.Write(ex.ToString());
return(StatusCode(500));
}
}
public IActionResult Lookup(string id)
{
try {
DbResult result;
using (var db = new AccessControlDatabase()) {
var sql = @"
SELECT
r.name,
r.timeout,
r.enabled,
g.name AS groupName,
r.address
FROM
reader r
INNER JOIN `group` g
ON r.group_id = g.group_id
WHERE
r.reader_id = @0
LIMIT 1;" ;
result = db.SingleOrDefault <DbResult>(sql, id);
}
if (result == null)
{
return(StatusCode(403));
}
var clientAddress = HttpContext.Connection.RemoteIpAddress.ToString();
if (result.address != clientAddress)
{
RecordClientAddress(id, clientAddress);
}
var output = new ReaderResult {
Name = result.name,
Timeout = result.timeout,
Enabled = result.enabled,
Group = result.groupName,
};
return(new JsonResult(output));
}
catch {
return(StatusCode(500));
}
}
private void RecordClientAddress(string id, string address)
{
using (var db = new AccessControlDatabase()) {
db.Execute(@"
UPDATE
reader
SET
address = @0
WHERE
reader_id = @1
LIMIT 1;" ,
address,
id);
}
}
private void RecordClient(int readerId, string address, string version, string status)
{
using (var db = new AccessControlDatabase()) {
db.Execute(@"
UPDATE
reader
SET
address = @0,
version = @1,
status = @2
WHERE
reader_id = @3
LIMIT 1;" ,
address,
version,
status,
readerId);
}
}
private AuthenticationResult Authenticate(string key, int readerId)
{
if (!string.IsNullOrEmpty(key))
{
int? groupId = null;
string sql = null;
using (var db = new AccessControlDatabase()) {
sql = @"
SELECT
group_id
FROM
reader
WHERE
reader_id = @0
AND enabled = 1
LIMIT 1;" ;
groupId = db.SingleOrDefault <int?>(sql, readerId);
if (groupId == null)
{
return(null);
}
//Support checking only the lower 26 bits of the key, because of stupid Wiegand protocol!
if (key.StartsWith("W26#"))
{
if (groupId != 0)
{
sql = @"
SELECT
m.member_id AS 'Id',
m.name AS 'Name',
m.type AS 'Type',
m.apricot_admin AS Admin,
m.joined AS Joined,
m.expires AS Expiration,
DATE_ADD(m.expires, INTERVAL 7 DAY) > NOW() AS AccessGranted
FROM
member m
INNER JOIN keycode k
ON m.member_id = k.member_id
INNER JOIN group_member gm
ON m.member_id = gm.member_id
WHERE
0x00FFFFFF & CONV(k.keycode_id, 16, 10) = CONV(@0, 16, 10)
AND gm.group_id = @1
LIMIT 1;" ;
}
else
{
sql = @"
SELECT
m.member_id AS 'Id',
m.name AS 'Name',
m.type AS 'Type',
m.apricot_admin AS Admin,
m.joined AS Joined,
m.expires AS Expiration,
DATE_ADD(m.expires, INTERVAL 7 DAY) > NOW() AS AccessGranted
FROM
member m
INNER JOIN keycode k
ON m.member_id = k.member_id
WHERE
0x00FFFFFF & CONV(k.keycode_id, 16, 10) = CONV(@0, 16, 10)
LIMIT 1;" ;
}
return(db.SingleOrDefault <AuthenticationResult>(sql, key.Substring(6), groupId));
}
else
{
if (groupId != 0)
{
sql = @"
SELECT
m.member_id AS 'Id',
m.name AS 'Name',
m.type AS 'Type',
m.apricot_admin AS Admin,
m.joined AS Joined,
m.expires AS Expiration,
DATE_ADD(m.expires, INTERVAL 7 DAY) > NOW() AS AccessGranted
FROM
member m
INNER JOIN keycode k
ON m.member_id = k.member_id
INNER JOIN group_member gm
ON m.member_id = gm.member_id
WHERE
(k.keycode_id = @0 OR k.keycode_id = @1)
AND gm.group_id = @2
LIMIT 1;" ;
}
else
{
sql = @"
SELECT
m.member_id AS 'Id',
m.name AS 'Name',
m.type AS 'Type',
m.apricot_admin AS Admin,
m.joined AS Joined,
m.expires AS Expiration,
DATE_ADD(m.expires, INTERVAL 7 DAY) > NOW() AS AccessGranted
FROM
member m
INNER JOIN keycode k
ON m.member_id = k.member_id
WHERE
(k.keycode_id = @0 OR k.keycode_id = @1)
LIMIT 1;" ;
}
// Check for older style keys with the trailing # in the database
return(db.SingleOrDefault <AuthenticationResult>(sql, key, $"{key}#", groupId));
}
}
}
return(null);
}
public async Task <Attempt[]> List(int page)
{
await Task.Yield();
Attempt[] list = null;
List <DbListResult> results = null;
using (var db = new AccessControlDatabase()) {
var sql = @"
SELECT
a.attempt_id,
a.attempt_time,
r.name AS reader,
m.name AS member,
a.access_granted,
a.login,
a.logout,
a.action
FROM
attempt a
INNER JOIN reader r
ON a.reader_id = r.reader_id
INNER JOIN member m
ON a.member_id = m.member_id
ORDER BY
a.attempt_time DESC
LIMIT
@0, @1" ;
results = await db.FetchAsync <DbListResult>(sql, page *pageSize, pageSize);
}
list = new Attempt[results.Count];
for (int i = 0; i < list.Length; i++)
{
var item = new Attempt {
Id = results[i].attempt_id,
Time = results[i].attempt_time,
Reader = results[i].reader,
Member = results[i].member,
};
if (results[i].access_granted)
{
item.Action = "";
}
else
{
item.Action = "Denied: ";
}
if (!string.IsNullOrEmpty(results[i].action))
{
item.Action += results[i].action;
}
else if (results[i].login)
{
item.Action += "Login";
}
else if (results[i].logout)
{
item.Action += "Logout";
}
else
{
item.Action += "N/A (Legacy Reader)";
}
list[i] = item;
}
return(list);
}
public Task <attempt> Get(int id)
{
using (var db = new AccessControlDatabase()) {
return(db.SingleAsync <attempt>(id));
}
}
