/// <summary>
/// Retrieve a lsit of <c>Location</c> one page at a time. Change the value of page and pageSize to alter the amount of
/// <c>Location</c> returned on each query.
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="minval"></param>
/// <returns></returns>
public IEnumerable <Location> GetPage(int page, int pageSize, DateTime minval)
{
var list = new List <Location>();
minval = minval == null ? new DateTime(year: 2000, month: 0, day: 0) : minval;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
// NOTE: This hard-coding a minimum date value is a temporary fix. Later on, we
// want to change this to some minimum value of the TIMESTAMP data type.
list = Location.Translate(
db.Fetch <LocationDto>(new NPoco.Sql(
$@"select *
from {_TABLE_NAME_}
where datecreated > TIMESTAMP '2000-01-01'
order by datecreated desc
limit {pageSize}"
))
).ToList();
db.Connection.Close();
}
}
return(list);
}
public static void Initialize()
{
var testDBType = Convert.ToInt32(ConfigurationManager.AppSettings["TestDBType"]);
switch (testDBType)
{
case 1: // SQLite In-Memory
throw new NotSupportedException("SQLite not supported DB type.");
//return;
case 2: // SQL Local DB
_dbDirect = new SqlLocalDatabase(ConfigurationManager.ConnectionStrings["ConStr_Primary"].ConnectionString);
_db = new Database(_dbDirect.Connection, new NPoco.DatabaseTypes.SqlServer2008DatabaseType(), IsolationLevel.ReadUncommitted); // Need read uncommitted for the transaction tests
break;
case 3: // SQL Server
case 4: // SQL CE
case 5: // MySQL
case 6: // Oracle
case 7: // Postgres
default:
throw new NotSupportedException("Unknown database platform specified.");
//return;
}
// Insert test data
TestData.RecreateData(_db);
}
public List <_Cep> FetchAll()
{
using (Database db = new NPoco.Database("Teste"))
{
return(db.Fetch <_Cep>());
}
}
public static string VerifyRecordCountMatchForPocoType(Type pocoType, NPoco.Database db)
{
if (db == null)
{
return("No database. Run RecreateData");
}
string tableName = pocoType.Name + (pocoType.Name.StartsWith("Objects") ? "" : "s");
//InMemoryCompositeKeyObjects
var imList = typeof(TestData).GetProperty("InMemory" + tableName).GetValue(null, null);
int imCount = 0;
if (imList is IEnumerable)
{
var enumerator = ((IEnumerable)imList).GetEnumerator();
while (enumerator.MoveNext())
{
imCount += 1;
}
}
int dbCount = db.ExecuteScalar <int>("SELECT COUNT(*) FROM " + tableName + ";");
if (imCount != dbCount)
{
return(" For test of " + tableName + ": In Memory Count = " + imCount + "; but Db Count = " + dbCount);
}
if (imCount == 0)
{
return(" For test of " + tableName + ": In Memory and Db have no items.");
}
return("");
}
public void Delete(_Cep oAnexo)
{
using (Database db = new NPoco.Database("Teste"))
{
db.Delete <_Cep>(oAnexo);
}
}
public static void Initialize()
{
var testDBType = Convert.ToInt32(ConfigurationManager.AppSettings["TestDBType"]);
switch (testDBType)
{
case 1: // SQLite In-Memory
throw new NotSupportedException("SQLite not supported DB type.");
//return;
case 2: // SQL Local DB
_dbDirect = new SqlLocalDatabase(ConfigurationManager.ConnectionStrings["ConStr_Primary"].ConnectionString);
_db = new Database(_dbDirect.Connection, new NPoco.DatabaseTypes.SqlServer2008DatabaseType(), IsolationLevel.ReadUncommitted); // Need read uncommitted for the transaction tests
break;
case 3: // SQL Server
case 4: // SQL CE
case 5: // MySQL
case 6: // Oracle
case 7: // Postgres
default:
throw new NotSupportedException("Unknown database platform specified.");
//return;
}
// Insert test data
TestData.RecreateData(_db);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FormLinkedRules_Load(object sender, System.EventArgs e)
{
Regex regex = new Regex("flowbits:isset,(.*?);", RegexOptions.IgnoreCase);
Match match = regex.Match(_rule);
if (match.Success == false)
{
UserInterface.DisplayMessageBox(this, "The rule does not contain the flowbits:set parameter", MessageBoxIcon.Exclamation);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
return;
}
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection(), DatabaseType.MySQL))
{
List <Rule> temp = db.Fetch <Rule>("SELECT * FROM rule WHERE rule LIKE @0", new object[] { string.Format("%flowbits:set,{0};%", match.Groups[1].Value.Trim()) });
listLinkedRules.SetObjects(temp);
if (temp.Count > 0)
{
listLinkedRules.SelectedObject = temp[0];
olvRule.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
listLinkedRules.Select();
}
}
}
public _Cep Get(int IdAnexo)
{
using (Database db = new NPoco.Database("Teste"))
{
return(db.SingleOrDefaultById <_Cep>(IdAnexo));
}
}
public _Cep Update(_Cep oAnexo)
{
using (Database db = new NPoco.Database("Teste"))
{
db.Update(oAnexo);
return(oAnexo);
}
}
public _Cep Insert(_Cep oAnexo)
{
using (Database db = new NPoco.Database("Teste"))
{
db.Insert <_Cep>(oAnexo);
return(oAnexo);
}
}
/// <summary>
/// Batch inserts a collection of <c>Location</c> objects.
/// </summary>
/// <param name="locations"></param>
public void Insert(IEnumerable <Location> locations)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
db.InsertBatch <LocationDto>(LocationDto.Translate(locations.ToList()));
db.Connection.Close();
}
}
}
static DbLocal( )
{
try
{
_db = new NPoco.Database(Properties.Settings.Default.DbConnection,
Properties.Settings.Default.DbProvider);
}
catch (Exception e) {
Debug.Write("Cannot connect to database.");
throw;
}
}
/// <summary>
/// Insert a single <c>Location</c> object.
/// </summary>
/// <param name="location"></param>
public void Insert(Location location)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
db.Save <LocationDto>(LocationDto.Translate(location));
db.Connection.Close();
}
}
}
/// <summary>
/// Batch inserts a collection of <c>Target</c> objects.
/// </summary>
/// <param name="targets"></param>
public void Insert(IEnumerable <Target> targets)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
db.InsertBatch <TargetDto>(TargetDto.Translate(targets.ToList()));
db.Connection.Close();
}
}
}
public void CanCreateAndSave()
{
var name = $"test name: {nameof(CanCreateAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var scan = ModelBuilders.CreateScan(name);
using (var db = new NPoco.Database(connection, DatabaseType.SQLite))
{
db.Insert(scan);
var result = db.SingleById <Scan>(scan.Id);
Assert.Equal(name, result.Name);
}
}
private DBManager()
{
if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
{
m_jwtPath = Path.Combine(Directory.GetCurrentDirectory(), "/" + m_dataPath + "/jwt.json");
m_dbPath = Path.Combine(Directory.GetCurrentDirectory(), "/" + m_dataPath + "/data.db");
}
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
var dataPath = Path.Combine(Directory.GetCurrentDirectory(), m_dataPath);
if (!Directory.Exists(dataPath))
{
Directory.CreateDirectory(dataPath);
}
m_jwtPath = Path.Combine(Directory.GetCurrentDirectory(), m_dataPath + "/jwt.json");
m_dbPath = Path.Combine(Directory.GetCurrentDirectory(), m_dataPath + "/data.db");
}
if (!File.Exists(m_jwtPath))
{
var bytes = new byte[128];
var ranndom = new Random();
ranndom.NextBytes(bytes);
var key = Convert.ToBase64String(bytes);
JWT = new JwtInfo()
{
ClockSkew = 10,
Audience = "Inotify",
Issuer = "Inotify",
IssuerSigningKey = key,
Expiration = 36000,
};
File.WriteAllText(m_jwtPath, JsonConvert.SerializeObject(JWT));
}
else
{
JWT = JsonConvert.DeserializeObject <JwtInfo>(File.ReadAllText(m_jwtPath));
}
m_dbConnection = new SqliteConnection(string.Format("Data Source={0}", m_dbPath));
if (m_dbConnection.State == ConnectionState.Closed)
{
m_dbConnection.Open();
}
DBase = new NPoco.Database(m_dbConnection, DatabaseType.SQLite);
m_migrator = new Migrator(DBase);
}
/// <summary>
/// Insert a single <c>Target</c> object.
/// </summary>
/// <param name="target"></param>
public void Insert(Target target)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
var dto = TargetDto.Translate(target);
db.Connection.Open();
db.Save <TargetDto>(TargetDto.Translate(target));
db.Connection.Close();
}
}
}
/// <summary>
/// Insert a single <c>Person</c> object.
/// </summary>
/// <param name="person"></param>
public void Insert(Person person)
{
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
PersonDto pdto = PersonDto.Translate(person);
pdto.DateCreated = DateTime.Now;
db.Save <PersonDto>(pdto);
db.Connection.Close();
}
}
}
/// <summary>
/// Deletes a <c>LocationDto</c> object.
/// </summary>
/// <param name="location">A <c>Location</c>. This will be translated to a <c>LocationDto</c> before the update operation.</param>
/// <returns><c>int</c> denoting the success value of the operation.</returns>
public int Remove(Location location)
{
int res;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
res = db.Delete(LocationDto.Translate(location));
db.Connection.Close();
}
}
return(res);
}
/// <summary>
/// Partial updates for <c>LocationDto</c> by only updating the specified columns.
/// </summary>
/// <param name="docId"></param>
/// <param name="data"></param>
/// <param name="ops"></param>
/// <returns></returns>
internal int PartialUpdate(Guid docId, Location data, IEnumerable <string> ops)
{
int res;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
res = db.Update(LocationDto.Translate(data), docId, ops);
db.Connection.Close();
}
}
return(res);
}
/// <summary>
/// Update a <c>PersonDto</c> object.
/// </summary>
/// <param name="device">A <c>Person</c>. This will be translated to a <c>PersonDto</c> before the update operation.</param>
/// <returns><c>int</c> denoting the success value of the operation.</returns>
public int Update(Person device)
{
int res;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
res = db.Update(PersonDto.Translate(device));
db.Connection.Close();
}
}
return(res);
}
/// <summary>
/// Partial update for <c>TargetDto</c> by only updating the specified columns.
/// </summary>
/// <param name="docId"></param>
/// <param name="target"></param>
/// <param name="ops">A <c>string</c> collection </param>
/// <returns></returns>
public int PartialUpdate(Guid docId, Target target, IEnumerable <string> ops)
{
int res;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
res = db.Update(TargetDto.Translate(target), docId, ops);
db.Connection.Close();
}
}
return(res);
}
/// <summary>
/// Updates a <c>TargetDto</c> object as the configured "chosen" Target.
/// </summary>
/// <param name="target"></param>
/// <returns></returns>
public int SetTarget(string target)
{
int res;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
//res = db.Update(TargetDto.Translate(target));
db.Connection.Close();
}
}
//return res;
return(0);
}
public void CanGetBackKindUtcFromDatabase()
{
// Microsoft.Data.Sqlite (as of 2.2.2) has problems storing/retrieving DateTime values as kind UTC
// https://system.data.sqlite.org/ -- works fine and returns UTC timestamp
// Update: NPoco/System.Data.SQLite seems to have trouble round-tripping UTC time to the database
// The fix for this is adding "datetimekind=Utc" to the connetion string
// "data source=\"file:mem-073d7ec1-528a-4482-8264-caf718d4fc06?mode=memory&cache=shared\";datetimekind=Utc"
var name = $"test message: {nameof(CanGetBackKindUtcFromDatabase)} {DateTimeOffset.UtcNow.Ticks}";
var scan = ModelBuilders.CreateScan(name);
scan.ScanFinished = new DateTime(2018, 12, 13, 22, 55, 43, DateTimeKind.Utc);
using (var db = new NPoco.Database(connection, DatabaseType.SQLite))
{
db.Insert(scan);
var result = db.SingleById <Scan>(scan.Id);
Assert.Equal(DateTimeKind.Utc, result.ScanFinished?.Kind);
Assert.Equal(scan.ScanFinished, result.ScanFinished);
}
}
public void CanCreateMultiplesAndSave()
{
var scanName = $"test scanName: {nameof(CanCreateMultiplesAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var scan = ModelBuilders.CreateScan(scanName);
string lastFolderName = null;
long lastFileId = 0;
string lastFileName = null;
using (var db = new NPoco.Database(connection, DatabaseType.SQLite))
{
db.Insert(scan);
for (var i = 0; i <= 9; i++)
{
var folder = ModelBuilders.CreateFolderScan($"Folder-{i}-{Guid.NewGuid()}", string.Empty);
folder.ScanId = scan.Id;
db.Insert(folder);
lastFolderName = folder.Name;
for (var j = 0; j <= 9; j++)
{
var file = ModelBuilders.CreateFileScan($"File-{i}-{j}-{Guid.NewGuid()}", string.Empty);
file.ScanId = scan.Id;
file.ParentFolderId = folder.Id;
db.Insert(file);
lastFileId = file.Id;
lastFileName = file.Name;
}
}
var fileResult = db.SingleById <FileScan>(lastFileId);
Assert.Equal(lastFileName, fileResult.Name);
var folderResult = db.SingleById <FolderScan>(fileResult.ParentFolderId);
Assert.Equal(lastFolderName, folderResult.Name);
}
}
/// <summary>
/// Retrieve a single <c>Location</c>
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Location GetSingle(Guid id)
{
Location location;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
location = Location.Translate(
db.Fetch <LocationDto>(new Sql(
$@"
select *
from {_TABLE_NAME_}
where id = @0
", id)).FirstOrDefault()
);
db.Connection.Close();
}
}
return(location);
}
/// <summary>
/// Retrieve a single <c>Target</c> from the database using <c>TargetDto</c> as a mediator.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Target GetSingle(Guid id)
{
Target target;
using (var conn = new NpgsqlConnection(dbAccess.connectionString))
{
using (var db = new NPoco.Database(conn))
{
db.Connection.Open();
target = Target.Translate(
db.Fetch <TargetDto>(new NPoco.Sql(
$@"
select *
from {_TABLE_NAME_}
where id = @0
", id)).FirstOrDefault()
);
db.Connection.Close();
}
}
return(target);
}
public void CanCreateAndSave()
{
var scanName = $"test scanName: {nameof(CanCreateAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var folderName = $"test folderName: {nameof(CanCreateAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var folderUri = $"test folderUri: {nameof(CanCreateAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var fileName = $"test fileName: {nameof(CanCreateAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var fileUri = $"test fileUri: {nameof(CanCreateAndSave)} {DateTimeOffset.UtcNow.Ticks}";
var scan = ModelBuilders.CreateScan(scanName);
var folder = ModelBuilders.CreateFolderScan(folderName, folderUri);
var file = ModelBuilders.CreateFileScan(fileName, fileUri);
using (var db = new NPoco.Database(connection, DatabaseType.SQLite))
{
db.Insert(scan);
folder.ScanId = scan.Id;
db.Insert(folder);
file.ScanId = scan.Id;
file.ParentFolderId = folder.Id;
db.Insert(file);
var result = db.SingleById <FileScan>(file.Id);
Assert.Equal(fileName, result.Name);
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FormLinkedRules_Load(object sender, System.EventArgs e)
{
Regex regex = new Regex("flowbits:isset,(.*?);", RegexOptions.IgnoreCase);
Match match = regex.Match(_rule);
if (match.Success == false)
{
UserInterface.DisplayMessageBox(this, "The rule does not contain the flowbits:set parameter", MessageBoxIcon.Exclamation);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
return;
}
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection(), DatabaseType.MySQL))
{
List<Rule> temp = db.Fetch<Rule>("SELECT * FROM rule WHERE rule LIKE @0", new object[] { string.Format("%flowbits:set,{0};%", match.Groups[1].Value.Trim()) });
listLinkedRules.SetObjects(temp);
if (temp.Count > 0)
{
listLinkedRules.SelectedObject = temp[0];
olvRule.AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
listLinkedRules.Select();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="dateFrom"></param>
/// <param name="initials"></param>
/// <param name="text"></param>
public void ExportAcknowledgmentsFrom(string filePath,
string dateFrom,
string initials,
bool text)
{
if (IsRunning == true)
{
OnExclamation("Already performing an export");
return;
}
IsRunning = true;
new Thread(() =>
{
try
{
if (text == true)
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
using (FileStream fileStream = new FileStream(filePath, FileMode.Append, FileAccess.Write))
using (StreamWriter streamWriter = new StreamWriter(fileStream))
{
string query = _sql.GetQuery(snorbert.Configs.Sql.Query.SQL_ACKNOWLEDGEMENT);
query = query.Replace("#WHERE#", @"WHERE acknowledgment.initials = @0
AND acknowledgment.timestamp > @1");
var data = db.Fetch<Dictionary<string, object>>(query, new object[] { initials, dateFrom });
foreach (Dictionary<string, object> temp in data)
{
streamWriter.WriteLine("Signature: " + temp["sig_name"].ToString());
streamWriter.WriteLine("SID/GID: " + temp["sig_sid"].ToString() + "/" + temp["sig_gid"].ToString());
if (temp["successful"] == null)
{
streamWriter.WriteLine("Acknowledgement: " + temp["description"].ToString());
}
else
{
streamWriter.WriteLine("Acknowledgement: " + temp["description"].ToString() + " (" + temp["successful"].ToString() + ")");
}
streamWriter.WriteLine("Notes: " + temp["notes"].ToString());
streamWriter.WriteLine(string.Empty);
}
OnComplete();
}
}
else
{
CsvConfiguration csvConfiguration = new CsvConfiguration();
csvConfiguration.Delimiter = '\t';
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
using (FileStream fileStream = new FileStream(filePath, FileMode.Append, FileAccess.Write))
using (StreamWriter streamWriter = new StreamWriter(fileStream))
using (CsvHelper.CsvWriter csvWriter = new CsvHelper.CsvWriter(streamWriter, csvConfiguration))
{
string query = _sql.GetQuery(snorbert.Configs.Sql.Query.SQL_ACKNOWLEDGEMENT);
query = query.Replace("#WHERE#", @"WHERE acknowledgment.initials = @0
AND acknowledgment.timestamp > @1");
var data = db.Fetch<Dictionary<string, object>>(query, new object[] { initials, dateFrom });
// Write out the file headers
csvWriter.WriteField("Sig Name");
csvWriter.WriteField("Sig SID");
csvWriter.WriteField("Sig GID");
csvWriter.WriteField("Acknowledgement");
csvWriter.WriteField("Notes");
csvWriter.NextRecord();
foreach (Dictionary<string, object> temp in data)
{
csvWriter.WriteField(temp["sig_name"].ToString());
csvWriter.WriteField(temp["sig_sid"].ToString());
csvWriter.WriteField(temp["sig_gid"].ToString());
if (temp["successful"] == null)
{
csvWriter.WriteField(temp["description"].ToString());
}
else
{
csvWriter.WriteField(temp["description"].ToString() + " (" + temp["successful"].ToString() + ")");
}
csvWriter.WriteField(temp["notes"].ToString());
csvWriter.NextRecord();
}
OnComplete();
}
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the export: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
/// <summary>
///
/// </summary>
/// <param name="filePath">The output file name</param>
public void ExportExcludes(string filePath)
{
if (IsRunning == true)
{
OnExclamation("Already performing an export");
return;
}
IsRunning = true;
new Thread(() =>
{
try
{
List<Exclude> excludes = new List<Exclude>();
using (NPoco.Database dbMySql = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
var data = dbMySql.Fetch<Dictionary<string, object>>(_sql.GetQuery(snorbert.Configs.Sql.Query.SQL_EXCLUDES));
foreach (Dictionary<string, object> temp in data)
{
Exclude exclude = new Exclude();
exclude.Id = long.Parse(temp["id"].ToString());
exclude.SigId = long.Parse(temp["sig_id"].ToString());
exclude.SigSid = long.Parse(temp["sig_sid"].ToString());
exclude.Rule = temp["sig_name"].ToString();
exclude.SourceIpText = temp["ip_src"].ToString();
exclude.DestinationIpText = temp["ip_dst"].ToString();
if (((byte[])temp["fp"])[0] == 48)
{
exclude.FalsePositive = false;
}
else
{
exclude.FalsePositive = true;
}
exclude.Timestamp = DateTime.Parse(temp["timestamp"].ToString());
excludes.Add(exclude);
}
}
CsvConfiguration csvConfiguration = new CsvConfiguration();
csvConfiguration.Delimiter = '\t';
using (FileStream fileStream = new FileStream(filePath, FileMode.Append, FileAccess.Write))
using (StreamWriter streamWriter = new StreamWriter(fileStream))
using (CsvHelper.CsvWriter csvWriter = new CsvHelper.CsvWriter(streamWriter, csvConfiguration))
{
// Write out the file headers
csvWriter.WriteField("Sig. ID");
csvWriter.WriteField("Source IP");
csvWriter.WriteField("Destination IP");
csvWriter.WriteField("FP");
csvWriter.WriteField("Comment");
csvWriter.WriteField("Sig. Name");
csvWriter.WriteField("Timestamp");
csvWriter.WriteField("Sig.");
csvWriter.NextRecord();
foreach (var temp in excludes)
{
csvWriter.WriteField(temp.SigId);
csvWriter.WriteField(temp.SourceIpText);
csvWriter.WriteField(temp.DestinationIpText);
csvWriter.WriteField(temp.FalsePositive);
csvWriter.WriteField(temp.Comment);
csvWriter.WriteField(temp.Rule);
csvWriter.WriteField(temp.Timestamp);
csvWriter.WriteField(temp.Rule);
csvWriter.NextRecord();
}
}
OnComplete();
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the export: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
public static void RecreateData(NPoco.Database db)
{
InMemoryCompositeKeyObjects = new List <CompositeKeyObject>();
InMemoryIdentityObjects = new List <IdentityObject>();
InMemoryKeyedGuidObjects = new List <KeyedGuidObject>();
InMemoryKeyedIntObjects = new List <KeyedIntObject>();
InMemoryListObjects = new List <ListObject>();
InMemoryNoKeyNonDistinctObjects = new List <NoKeyNonDistinctObject>();
InMemoryObjectsWithCustomType = new List <ObjectsWithCustomType>();
// Clear out any old items
db.Execute("TRUNCATE TABLE CompositeKeyObjects;");
db.Execute("TRUNCATE TABLE IdentityObjects;");
db.Execute("TRUNCATE TABLE KeyedGuidObjects;");
db.Execute("TRUNCATE TABLE KeyedIntObjects;");
db.Execute("TRUNCATE TABLE ListObjects;");
db.Execute("TRUNCATE TABLE NoKeyNonDistinctObjects;");
db.Execute("DBCC CHECKIDENT ('IdentityObjects', RESEED, 0) WITH NO_INFOMSGS;");
int pos;
for (var i = 0; i < 15; i += 1)
{
pos = i + 1;
var cko = new CompositeKeyObject
{
Key1ID = (pos / 5) + 1,
Key2ID = (pos / 3) + 1,
Key3ID = (pos % 5),
TextData = "Text" + pos,
DateEntered = new DateTime(1970, 1, 1).AddYears(pos),
DateUpdated = DateTime.Now
};
db.Insert(cko);
InMemoryCompositeKeyObjects.Add(cko);
var io = new IdentityObject
{
Name = "Name " + pos,
Age = (pos % 2 == 0) ? (int?)pos * 3 : null,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(pos),
Savings = (pos * 100000) / 13,
DependentCount = (pos % 2 == 1) ? (byte?)pos : null,
Gender = (pos % 2 == 0) ? "M" : "F",
IsActive = (pos % 2 == 1)
};
db.Insert(io);
InMemoryIdentityObjects.Add(io);
var kgo = new KeyedGuidObject
{
Id = System.Guid.NewGuid(),
Name = "Name " + pos,
Age = (pos % 2 == 0) ? (int?)pos * 3 : null,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(pos),
Savings = (pos * 100000) / 13,
DependentCount = (pos % 2 == 1) ? (byte?)pos : null,
Gender = (pos % 2 == 0) ? "M" : "F",
IsActive = (pos % 2 == 1)
};
db.Insert(kgo);
InMemoryKeyedGuidObjects.Add(kgo);
var kio = new KeyedIntObject
{
Id = pos,
Name = "Name " + pos,
Age = (pos % 2 == 0) ? (int?)pos * 3 : null,
DateOfBirth = new DateTime(1970, 1, 1).AddYears(pos),
Savings = (pos * 100000) / 13,
DependentCount = (pos % 2 == 1) ? (byte?)pos : null,
Gender = (pos % 2 == 0) ? "M" : "F",
IsActive = (pos % 2 == 1)
};
db.Insert(kio);
InMemoryKeyedIntObjects.Add(kio);
var nkndo = new NoKeyNonDistinctObject
{
FullName = "Name " + pos % 2,
ItemInt = pos % 2,
OptionalInt = (pos % 2 == 0) ? (int?)42 : null,
Color = (pos % 2 == 0) ? "Red" : "Blue"
};
db.Insert(nkndo);
InMemoryNoKeyNonDistinctObjects.Add(nkndo);
var owct = new ObjectsWithCustomType
{
Id = "StringId_" + pos.ToString(),
Name = "Blah",
MySpecialTypeField = new DateTime(1925 + pos, 2, 15)
};
db.Insert(owct);
InMemoryObjectsWithCustomType.Add(owct);
}
InMemoryListObjects.Add(new ListObject {
Id = 1,
ShortName = "LiveLetter",
Description = "Live with Letter of Intent Only",
StatusKey = "A",
SortOrder = 1
});
InMemoryListObjects.Add(new ListObject
{
Id = 2,
ShortName = "TakesPatients",
Description = "Will Accept Most Patients",
StatusKey = "P",
SortOrder = 10
});
InMemoryListObjects.Add(new ListObject
{
Id = 3,
ShortName = "Active",
Description = "Active",
StatusKey = "I",
SortOrder = 20
});
InMemoryListObjects.Add(new ListObject
{
Id = 4,
ShortName = "Prospect",
Description = "Prospect",
StatusKey = "I",
SortOrder = 30
});
InMemoryListObjects.Add(new ListObject
{
Id = 5,
ShortName = "OnHold",
Description = "Tracked but not expected to participate",
StatusKey = "I",
SortOrder = 90
});
foreach (var lo in InMemoryListObjects)
{
db.Insert(lo);
}
}
/// <summary>
///
/// </summary>
/// <param name="filePath">The output file name</param>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <param name="sid"></param>
public void ExportEventsAll(string filePath,
string dateFrom,
string dateTo,
string sid)
{
if (IsRunning == true)
{
OnExclamation("Already performing an export");
return;
}
IsRunning = true;
new Thread(() =>
{
try
{
using (NPoco.Database db = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
string query = _sql.GetQuery(snorbert.Configs.Sql.Query.SQL_RULES_EVENTS_EXPORT);
query = query.Replace("#WHERE#", @"WHERE exclude.id IS NULL
AND event.timestamp > @0
AND event.timestamp < @1
AND signature.sig_sid = @2");
List<Event> events = db.Fetch<Event>(query, new object[] { dateFrom, dateTo, sid });
events = Helper.ProcessEventDataSet(events);
CsvConfiguration csvConfiguration = new CsvConfiguration();
csvConfiguration.Delimiter = '\t';
using (FileStream fileStream = new FileStream(filePath, FileMode.Append, FileAccess.Write))
using (StreamWriter streamWriter = new StreamWriter(fileStream))
using (CsvHelper.CsvWriter csvWriter = new CsvHelper.CsvWriter(streamWriter, csvConfiguration))
{
// Write out the file headers
csvWriter.WriteField("CID");
csvWriter.WriteField("Src IP");
csvWriter.WriteField("Src Port");
csvWriter.WriteField("Dst IP");
csvWriter.WriteField("Dst Port");
csvWriter.WriteField("Protocol");
csvWriter.WriteField("Timestamp");
csvWriter.WriteField("TCP Flags");
csvWriter.WriteField("Payload (ASCII)");
csvWriter.WriteField("Payload (HEX)");
csvWriter.NextRecord();
foreach (var item in events)
{
csvWriter.WriteField(item.Cid);
csvWriter.WriteField(item.IpSrcTxt);
csvWriter.WriteField(item.SrcPort);
csvWriter.WriteField(item.IpDst);
csvWriter.WriteField(item.DstPort);
csvWriter.WriteField(item.Protocol);
csvWriter.WriteField(item.Timestamp);
csvWriter.WriteField(item.TcpFlagsString);
csvWriter.WriteField(item.PayloadAscii);
csvWriter.WriteField( Helper.ConvertByteArrayToHexString(item.PayloadHex));
csvWriter.NextRecord();
}
}
OnComplete();
}
}
catch (Exception ex)
{
OnError("An error occurred whilst performing the export: " + ex.Message);
}
finally
{
IsRunning = false;
}
}).Start();
}
public CsvImporter()
{
_db = DB.Database.GetDatabase();
}
public API() : base("/")
{
//Get Base.
Get["/"] = parameters =>
{
return("<h1>API FLYPATH</h1><br><h2>Taules:</h2><br>1. Rutes<br>2. Usuaris<br>3. Amistats<br>" +
"4. Estats");
};
//establex la ruta de al connexio a al base
var connexio = @"Data Source=192.168.100.11;Initial Catalog=FlyPath;User ID=sa;Password=123";
//Error 500, InternalServerError.
Response my500 = new Response
{
StatusCode = HttpStatusCode.InternalServerError
};
//Error 41, Unauthorized.
Response my401 = new Response
{
StatusCode = HttpStatusCode.Unauthorized
};
//Error 404, NotFound.
Response my404 = new Response
{
StatusCode = HttpStatusCode.NotFound
};
//Error 400, BadRequest.
Response my400 = new Response
{
StatusCode = HttpStatusCode.BadRequest
};
//GET : Per obtenir dades i en la funcionalitat de cerca.
//POST : Per afegir dades
//PUT : Per actualitzar dades
//DELETE : Per eliminar dades
//////////////////////////////////USUARIS////////////////////////////////////
//Get tots els usuaris.
Get["usuaris"] = parameters =>
{
try
{
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myData = myCon.Fetch <Usuari>();
String llista = "<h1>Llista: </h1><br>";
foreach (var item in myData)
{
llista += item + "<br>";
}
return(fastJSON.JSON.ToJSON(myData));
}
}
catch (Exception)
{
return(my500);
}
};
//Get un usuari amb el seu codi.
Get["usuari/{codi}"] = parameters =>
{
try
{
string _myUser = parameters.codi;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myCon.Exists <Usuari>(_myUser))
{
var myData = myCon.Fetch <Usuari>(NPoco.Sql.Builder.Where("id=@0", _myUser));
String llista = "<h1>Llista: </h1><br>";
foreach (var item in myData)
{
llista += item + "<br>";
}
return(llista);
}
else
{
return(my400);
}
}
}
catch (Exception)
{
return(my500);
}
};
//Get comprovar usuari existeix i fer el login
Get["usuariLogin/{Username}/{Password}"] = parameters =>
{
try
{
string myUsername = parameters.Username;
string myPassword = parameters.Password;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myUsername != "" && myPassword != "" && myUsername != null && myPassword != null)
{
var myData = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("Username=@0 AND Password=@1", myUsername, myPassword));
return(fastJSON.JSON.ToJSON(myData));
}
else
{
return(my400);
}
}
}
catch (Exception e)
{
String ee = e.Message;
return(my500);
}
};
//Post, crear registre d'un usuari.
Post["usuariRegistre"] = parameters =>
{
try
{
string myEmail = this.Request.Form["Email"];
string myUsername = this.Request.Form["Username"];
string myPassowrd = this.Request.Form["Password"];
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var allUsers = myCon.Fetch <Usuari>(NPoco.Sql.Builder.Where("Username=@0 AND Email=@1", myUsername, myEmail));
if (!(myEmail.Contains("@") && myEmail.Contains(".")))
{
return(my401);
}
else if (allUsers.Count == 0)
{
var myNouReg = new Usuari
{
Email = myEmail,
Username = myUsername,
Password = myPassowrd
};
myCon.Insert(myNouReg);
return(fastJSON.JSON.ToJSON(myNouReg));
}
else
{
return(my404);
}
}
}
catch (Exception e)
{
return(my500);
}
};
//put, actualitza les dades d'un usuari
Put["usuariCanvis"] = parameters =>
{
try
{
string myID = this.Request.Form["ID"];
string myEmail = this.Request.Form["Email"];
string myUsername = this.Request.Form["Username"];
string myPassowrd = this.Request.Form["Password"];
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
int myData1 = myCon.Fetch <Usuari>(NPoco.Sql.Builder.Where("Email=@0", myEmail)).Count;
int myData2 = myCon.Fetch <Usuari>(NPoco.Sql.Builder.Where("Username=@0", myUsername)).Count;
if (!(myEmail.Contains("@") && myEmail.Contains(".")))
{
return(my401);
}
else if (myData1 > 1 || myData2 > 1)//si hi ha un ets tu si son dos algu altre ja el te
{
return(my404);
}
else
{
var myNouReg = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", myID));
myNouReg.Email = myEmail;
myNouReg.Username = myUsername;
if (myPassowrd != null)
{
myNouReg.Password = myPassowrd;
}
myCon.Update(myNouReg);
return(fastJSON.JSON.ToJSON(myNouReg));
}
}
}
catch (Exception e)
{
return(my500);
}
};
//////////////////////////////////RUTES////////////////////////////////////
//get, obte totes les rutes amb estat public
Get["obtenirRutesPubliques"] = parameters =>
{
try
{
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myRutes = myCon.Fetch <Ruta>(NPoco.Sql.Builder.Where("Estat=@0", 2));
List <RutaDades> llista = new List <RutaDades>();
for (int i = 0; i < myRutes.Count; i++)
{
var creador = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", myRutes[i].Creador));
var estat = myCon.Single <Ruta_Estat>(NPoco.Sql.Builder.Where("Estat=@0", myRutes[i].Estat));
var myRDades = new RutaDades
{
ID = myRutes[i].ID,
Nom = myRutes[i].Nom,
Descripcio = myRutes[i].Descripcio,
CreadorUsername = creador.Username,
InfoRuta = myRutes[i].Info_Ruta,
Estat = estat.Descripcio
};
llista.Add(myRDades);
}
;
var llistaJSON = fastJSON.JSON.ToJSON(llista);
return(llistaJSON);
}
}
catch (Exception)
{
return(my500);
}
};
//get, obte totes les rutes amb estat privat, nomes les rutes que ell a fet i les privades
Get["obtenirRutesPrivades/{ID_Usuari}"] = parameters =>
{
try
{
int ID_Usuari = parameters.ID_Usuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myRutes = myCon.Fetch <Ruta>(NPoco.Sql.Builder.Where("Creador=@0", ID_Usuari));
List <RutaDades> llista = new List <RutaDades>();
for (int i = 0; i < myRutes.Count; i++)
{
var creador = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", myRutes[i].Creador));
var estat = myCon.Single <Ruta_Estat>(NPoco.Sql.Builder.Where("Estat=@0", myRutes[i].Estat));
var myRDades = new RutaDades
{
ID = myRutes[i].ID,
Nom = myRutes[i].Nom,
Descripcio = myRutes[i].Descripcio,
CreadorUsername = creador.Username,
InfoRuta = myRutes[i].Info_Ruta,
Estat = estat.Descripcio
};
llista.Add(myRDades);
}
;
var llistaJSON = fastJSON.JSON.ToJSON(llista);
return(llistaJSON);
}
}
catch (Exception)
{
return(my500);
}
};
//get, obte totes les rutes compartides que pot visualitzar l'usuari, nomes las rutes de usuaris que segueix
Get["obtenirRutesCompartides/{ID_Usuari}"] = parameters =>
{
try
{
int ID_Usuari = parameters.ID_Usuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myCon.Exists <Amistats>(ID_Usuari))
{
//obte les rutes en estat public
var rutesComp = myCon.Fetch <Ruta>(NPoco.Sql.Builder.Where("Estat=@0", 3));
List <RutaComp> rutesCompartides = new List <RutaComp>();
List <RutaComp> rutesCompartidesSeguits = new List <RutaComp>();
for (int i = 0; i < rutesComp.Count; i++)
{
int asd = rutesComp[i].Creador;
var user = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", rutesComp[i].Creador));
string userCreador = user.Username;
var myRuta = new RutaComp
{
ID = rutesComp[i].ID,
Nom = rutesComp[i].Nom,
Descripcio = rutesComp[i].Descripcio,
Creador = userCreador,
InfoRuta = rutesComp[i].Info_Ruta,
Estat = rutesComp[i].Estat
};
rutesCompartides.Add(myRuta);
}
;
//obte les rutes de seguits
var seguits = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("seguidor=@0", ID_Usuari));
for (int i = 0; i < rutesCompartides.Count; i++)
{
for (int y = 0; y < seguits.Count; y++)
{
var user = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", seguits[y].ID_Usuari));
String usernameSeguit = user.Username;
if (rutesCompartides[i].Creador.Equals(usernameSeguit))
{
rutesCompartidesSeguits.Add(rutesCompartides[i]);
}
}
}
var llistaJSON = fastJSON.JSON.ToJSON(rutesCompartidesSeguits);
return(llistaJSON);
}
else
{
return(my400);
}
}
}
catch (Exception)
{
return(my500);
}
};
//get obte el numero de rutes que ha compartit l'usuari
Get["numeroRutesCompartides/{ID_Usuari}"] = parameters =>
{
try
{
int ID_Usuari = parameters.ID_Usuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
//obte les rutes en estat public
var rutesComp = myCon.Fetch <Ruta>(NPoco.Sql.Builder.Where("Creador=@0 AND Estat=@1", ID_Usuari, 3));
if (rutesComp.Count() < 1)
{
return("0");
}
else
{
return(rutesComp.Count().ToString());
}
}
}
catch (Exception)
{
return(my500);
}
};
//get, obte l'informació d'una sola ruta
Get["obtenirRuta/{idRuta}"] = parameters =>
{
try
{
int idRuta = parameters.idRuta;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myCon.Exists <Ruta>(idRuta))
{
var myRuta = myCon.Fetch <Ruta>(NPoco.Sql.Builder.Where("id=@0", idRuta));
return(fastJSON.JSON.ToJSON(myRuta));
}
else
{
return(my400);
}
}
}
catch (Exception)
{
return(my500);
}
};
//Post, genera una nova ruta
Post["generarRuta"] = parameters => {
try
{
string myNom = this.Request.Form["Nom"];
string myDescripcio = this.Request.Form["Descripcio"];
int myCreador = Int32.Parse(this.Request.Form["Creador"]);
string myInfo_Ruta = this.Request.Form["Info_Ruta"];
int myEstat = Int32.Parse(this.Request.Form["Estat"]);
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myNouReg = new Ruta
{
Nom = myNom,
Descripcio = myDescripcio,
Creador = myCreador,
Info_Ruta = myInfo_Ruta,
Estat = myEstat
};
myCon.Insert(myNouReg);
return(fastJSON.JSON.ToJSON(myNouReg));
}
}
catch (Exception e)
{
String a = e.Message;
return(my500);
}
};
//Put, modifica una ruta ja existent
Put["modificarRuta"] = parameters => {
try
{
string myID = this.Request.Form["ID"];
string myNom = this.Request.Form["Nom"];
string myDescripcio = this.Request.Form["Descripcio"];
string myInfo_Ruta = this.Request.Form["Info_Ruta"];
string myEstat = this.Request.Form["Estat"];
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myNouReg = myCon.Single <Ruta>(NPoco.Sql.Builder.Where("ID=@0", myID));
myNouReg.Nom = myNom;
myNouReg.Descripcio = myDescripcio;
myNouReg.Info_Ruta = myInfo_Ruta;
myNouReg.Estat = Int32.Parse(myEstat);
myCon.Update(myNouReg);
return(fastJSON.JSON.ToJSON(myNouReg));
}
}
catch (Exception e)
{
String a = e.Message;
return(my500);
}
};
//Delete, elimina una ruta ja existent
Delete["eliminarRuta/{idRuta}"] = parameters => {
try
{
int idRuta = parameters.idRuta;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myNouReg = myCon.Single <Ruta>(NPoco.Sql.Builder.Where("ID=@0", idRuta));
myCon.Delete(myNouReg);
return("4 a zero");
}
}
catch (Exception e)
{
String a = e.Message;
return(my500);
}
};
///////////////////////////AMISTATS///////////////////////////
//get, obte els seguidors que te l'usuari
Get["seguidors/{idUsuari}"] = parameters =>
{
try
{
int idUsuari = parameters.idUsuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myCon.Exists <Amistats>(idUsuari))
{
var myAmistats = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("ID_Usuari=@0", idUsuari));
List <Usuari> usuaris = new List <Usuari>();
for (int i = 0; i < myAmistats.Count; i++)
{
usuaris.Add(myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", myAmistats[i].seguidor)));
}
return(fastJSON.JSON.ToJSON(usuaris));
}
else
{
return(my400);
}
}
}
catch (Exception e)
{
string a = e.Message;
return(my500);
}
};
//Get, obte els usuaris que segueix el usuari
Get["seguits/{idUsuari}"] = parameters =>
{
try
{
int idUsuari = parameters.idUsuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myAmistats = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("Seguidor=@0", idUsuari));
List <Usuari> usuaris = new List <Usuari>();
for (int i = 0; i < myAmistats.Count; i++)
{
usuaris.Add(myCon.Single <Usuari>(NPoco.Sql.Builder.Where("ID=@0", myAmistats[i].ID_Usuari)));
}
return(fastJSON.JSON.ToJSON(usuaris));
}
}
catch (Exception e)
{
string a = e.Message;
return(my500);
}
};
//get, obte el numero de seguidors que te l'usuari
Get["numeroSeguidors/{idUsuari}"] = parameters =>
{
try
{
int idUsuari = parameters.idUsuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myCon.Exists <Amistats>(idUsuari))
{
var myAmistats = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("ID_Usuari=@0", idUsuari));
return(myAmistats.Count().ToString());
}
else
{
return("0");
}
}
}
catch (Exception e)
{
string a = e.Message;
return(my500);
}
};
//get, obte el numero de seguidors,seguits i rutes compartides per un usuari
Get["numeroSeguidorsSeguitsRutesCompartides/{idUsuari}"] = parameters =>
{
try
{
int idUsuari = parameters.idUsuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
int seguidors = 0;
int seguits = 0;
int rutesCompartides = 0;
//seguidors usuari
var mySeguidors = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("ID_Usuari=@0", idUsuari));
if (mySeguidors.Count() < 1)
{
seguidors = 0;
}
else
{
seguidors = mySeguidors.Count();
}
//usuaris seguits
var mySeguits = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("Seguidor=@0", idUsuari));
if (mySeguits.Count() < 1)
{
seguits = 0;
}
else
{
seguidors = mySeguits.Count();
}
//rutes compartides
var rutesComp = myCon.Fetch <Ruta>(NPoco.Sql.Builder.Where("Creador=@0 AND Estat=@1", idUsuari, 3));
if (rutesComp.Count() < 1)
{
rutesCompartides = 0;
}
else
{
rutesCompartides = rutesComp.Count();
}
return(seguidors.ToString() + "," + seguits.ToString() + "," + rutesCompartides.ToString());
}
}
catch (Exception e)
{
string a = e.Message;
return(my500);
}
};
//get, obte el numero de seguits que te un usuari
Get["numeroSeguits/{idUsuari}"] = parameters =>
{
try
{
int idUsuari = parameters.idUsuari;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var myAmistats = myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("Seguidor=@0", idUsuari));
List <Usuari> usuaris = new List <Usuari>();
if (myAmistats.Count < 1)
{
return("0");
}
else
{
return(myAmistats.Count().ToString());;
}
}
}
catch (Exception e)
{
string a = e.Message;
return(my500);
}
};
//Post, afegeix un seguidor
Post["afegirSeguidor"] = parameters => {
try
{
string myUsername = this.Request.Form["Username"];
string mySeguidor = this.Request.Form["Seguidor"];
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
var Usuari = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("Username=@0", myUsername));
string UsuariID = Usuari.ID;
var Seguidor = myCon.Single <Usuari>(NPoco.Sql.Builder.Where("Username=@0", mySeguidor));
string SeguidorID = Seguidor.ID;
if (myCon.Fetch <Amistats>(NPoco.Sql.Builder.Where("ID_Usuari=@0 AND Seguidor=@1", UsuariID, SeguidorID)).Count >= 1)
{
//ja segueixes aket usuari
return(null);
}
else
{
var follower = new Amistats
{
ID_Usuari = UsuariID,
seguidor = SeguidorID
};
myCon.Insert(follower);
return(fastJSON.JSON.ToJSON(follower));
}
}
}
catch (Exception e)
{
String a = e.Message;
return(my500);
}
};
//delete, deixa de seguir a un usuari
Delete["deixarSeguir/{Username}/{Seguidor}"] = parameters => {
try
{
int myUsername = parameters.Seguidor;
int myUnFollow = parameters.Username;
using (NPoco.Database myCon = new NPoco.Database(connexio, NPoco.DatabaseType.SqlServer2012))
{
if (myCon.Exists <Amistats>(myUnFollow))
{
var unfollow = myCon.Single <Amistats>(NPoco.Sql.Builder.Where("ID_Usuari=@0 AND Seguidor=@1", myUnFollow, myUsername));
//ja segueixes aket usuari
myCon.Delete(unfollow);
return("4 a zero");
}
else
{
//no segueix l'usuari aquest
return(null);
}
}
}
catch (Exception e)
{
String a = e.Message;
return(my500);
}
};
//////////////////////////////////OPORTUNITATS////////////////////////////////////
/*
* //?user=Usuari3
* //Get totes les oportunitats.
* Get["oportunitat"] = parameters =>
* {
* var user = "";
* if (this.Request.Query["user"] != null)
* {
* user = this.Request.Query["user"];
* }
*
* try
* {
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"")
* {
* myData = myCon.Fetch<Oportunitat>("WHERE Usuari_ID=@0", user);
* }
* else
* {
* myData = myCon.Fetch<Oportunitat>("WHERE acabat like 0");
* }
*
* if (myData.Count > 1)
* {
* return fastJSON.JSON.ToJSON(myData);
* }
* else
* {
* return my404;
* }
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Get una oporutunitat.
*
* Get["oportunitat/{codi}"] = parameters =>
* {
* try
* {
* string myOp = parameters.codi;
*
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"id=@0", myOp));
* return fastJSON.JSON.ToJSON(myData);
*
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Post una Oportunitat.
* Post["oportunitat/{codi}"] = parameters =>
* {
* try
* {
* string myUserID = parameters.codi;
* PostUser pepito = fastJSON.JSON.ToObject<PostUser>(this.Request.Form["pepito"]);
*
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"ID=@0", myUserID)).Any())
* {
* var myData = myCon.Fetch<Oportunitat>();
* var myNouReg = new Oportunitat();
*
* myNouReg = new Oportunitat
* {
* Nom = pepito.Nom,
* Usuari_ID = myUserID,
* Data_Inici = DateTime.Today,
* };
* if (!myCon.Fetch<Oportunitat>(NPoco.Sql.Builder.Where("Nom=@0 and Client_ID=@1 and Estat=@2", pepito.Nom)).Any())
* {
* myCon.Insert(myNouReg);
* return myNouReg;
* }
* else
* {
* return my404;
* }
* }
* else
* {
* return my404;
* }
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Put una Oportunitat.
* Put["oportunitat/{usuari}/{codi}"] = parameters =>
* {
* try
* {
* string myID = parameters.codi;
* string myUserID = parameters.usuari;
* PostUser pepito = fastJSON.JSON.ToObject<PostUser>(this.Request.Form["pepito"]);
*
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"id=@0", myID));
*
* if (pepito.Nom != "" && pepito.Nom != null && pepito.Nom != myNouReg[0].Nom)
* {
* myNouReg[0].Nom = pepito.Nom;
* }
*
* myCon.Update(myNouReg[0]);
*
* myNouReg = myCon.Fetch<Oportunitat>(NPoco.Sql.Builder.Where("id=@0", myID));
* var result = new Oportunitat();
*
* foreach (var item in myNouReg)
* {
* result = item;
* }
* return result;
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Delete una oportunitat
* Delete["oportunitat/{codi}"] = parameters =>
* {
* try
* {
* string myID = parameters.codi;
*
* if (Int32.TryParse(myID, out int myID2))
* {
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"id=@0", myID));
*
* myCon.Delete(myNouReg[0]);
* return "";
* }
* else
* {
* return my404;
* }
* }
* }
* else
* {
* return my400;
* }
* }
* catch (Exception)
* {
* return my500;
* }
*
* };
*
* //////////////////////////////////ESTATS////////////////////////////////////
*
* //Get número d'estats.
* Get["estat/count"] = parameters =>
* {
* try
* {
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"";
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Get tots els estats.
* Get["estat"] = parameters =>
* {
* try
* {
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"estat/{codi}"] = parameters =>
* {
* try
* {
* string myEstat = parameters.codi;
*
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"id=@0", myEstat));
* return fastJSON.JSON.ToJSON(myData);
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Post un estat.
* Post["estat"] = parameters =>
* {
* try
* {
* string myNom = this.Request.Form["Nom"];
*
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"")
* {
* var myNouReg = new Estat
* {
* Nom = myNom
* };
*
* myCon.Insert(myNouReg);
* return myNouReg;
* }
* else
* {
* return my400;
* }
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
* //Put un estat.
* Put["estat/{codi}"] = parameters =>
* {
* try
* {
* string myID = parameters.codi;
* string myNom = this.Request.Form["Nom"];
*
* if (Int32.TryParse(myID, out int myID2))
* {
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"id=@0", myID2));
*
* if (myNom != myNouReg[0].Nom && myNom != "")
* {
* myNouReg[0].Nom = myNom;
* myCon.Update(myNouReg[0]);
*
* return myNouReg[0];
* }
* else
* {
* return my400;
* }
* }
* else
* {
* return my404;
* }
* }
* }
* else
* {
* return my400;
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*
*
* //Delete un Estat.
* Delete["estat/{codi}"] = parameters =>
* {
* try
* {
* int myID = parameters.codi;
*
* using (NPoco.Database myCon = new NPoco.Database(@"Data Source=192.168.110.79;Initial Catalog=oportunitats;User ID=sa;Password="******"WHERE Estat=@0", myID).Any())
* {
* if (myCon.Exists<Estat>(myID))
* {
* var myNouReg = myCon.Fetch<Estat>(NPoco.Sql.Builder.Where("id=@0", myID));
*
* myCon.Delete(myNouReg[0]);
* return "";
* }
* else
* {
* return my404;
* }
* }
* else
* {
* return my400;
* }
* }
* }
* catch (Exception)
* {
* return my500;
* }
* };
*/
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
public void DisplaySelectedEventDetails(Event temp)
{
using (new HourGlass(this))
{
if (temp.IpProto == (int)Global.Protocols.Tcp)
{
if (tabEvent.TabPages.Contains(tabPageTcpHeader) == false)
{
tabEvent.TabPages.Insert(2, tabPageTcpHeader);
}
if (tabEvent.TabPages.Contains(tabPageUdpHeader) == true)
{
tabEvent.TabPages.Remove(tabPageUdpHeader);
}
}
else if (temp.IpProto == (int)Global.Protocols.Udp)
{
if (tabEvent.TabPages.Contains(tabPageUdpHeader) == false)
{
tabEvent.TabPages.Insert(2, tabPageUdpHeader);
}
if (tabEvent.TabPages.Contains(tabPageTcpHeader) == true)
{
tabEvent.TabPages.Remove(tabPageTcpHeader);
}
}
else
{
tabEvent.TabPages.Remove(tabPageTcpHeader);
tabEvent.TabPages.Remove(tabPageUdpHeader);
}
// DNS Tab
if (temp.TcpDstPort == 53 || temp.TcpSrcPort == 53 || temp.UdpDstPort == 53 || temp.UdpSrcPort == 53)
{
if (tabEvent.TabPages.Contains(tabPageDns) == false)
{
tabEvent.TabPages.Insert(tabEvent.TabPages.Count - 2, tabPageDns);
}
} else {
tabEvent.TabPages.Remove(tabPageDns);
}
// IP Tab
ipSource.Text = temp.IpSrcTxt.ToString();
ipDest.Text = temp.IpDstTxt.ToString();
txtIpCsum.Text = temp.IpCsum.ToString();
txtIpFlags.Text = temp.IpFlags.ToString();
txtIpHlen.Text = temp.IpHlen.ToString();
txtIpId.Text = temp.IpId.ToString();
txtIpLen.Text = temp.IpLen.ToString();
txtIpOff.Text = temp.IpOff.ToString();
txtIpProto.Text = temp.IpProto.ToString();
txtIpTos.Text = temp.IpTos.ToString();
txtIpTtl.Text = temp.IpTtl.ToString();
txtIpVer.Text = temp.IpVer.ToString();
// Signature Tab
txtSigCategory.Text = temp.SigClassName;
txtSigGenId.Text = temp.SigGid.ToString();
txtSigSigRev.Text = temp.SigRev.ToString();
txtSigSigId.Text = temp.SigSid.ToString();
txtRule.Text = temp.Rule;
if (txtRule.Text.IndexOf("flowbits:isset,", StringComparison.InvariantCultureIgnoreCase) > -1)
{
btnLinkedRules.Enabled = true;
}
else
{
btnLinkedRules.Enabled = false;
}
// TCP Tab
txtTcpAck.Text = temp.TcpAck.ToString();
txtTcpCsum.Text = temp.TcpCsum.ToString();
txtTcpDstPort.Text = temp.TcpDstPort.ToString();
txtTcpFlags.Text = temp.TcpFlags.ToString();
txtTcpOff.Text = temp.TcpOff.ToString();
txtTcpRes.Text = temp.TcpRes.ToString();
txtTcpSeq.Text = temp.TcpSeq.ToString();
txtTcpSrcPrt.Text = temp.TcpSrcPort.ToString();
txtTcpUrp.Text = temp.TcpUrp.ToString();
txtTcpWin.Text = temp.TcpWin.ToString();
// UDP Tab
txtUdpSrcPort.Text = temp.UdpSrcPort.ToString();
txtUdpDstPort.Text = temp.TcpDstPort.ToString();
txtUdpLen.Text = temp.UdpLen.ToString();
txtUdpCsum.Text = temp.UdpCsum.ToString();
// References Tab
using (NPoco.Database dbMySql = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
List<Reference> references = dbMySql.Fetch<Reference>(_sql.GetQuery(snorbert.Configs.Sql.Query.SQL_REFERENCES), new object[] { temp.SigId });
listReferences.SetObjects(references);
}
ResizeReferenceListColumns();
txtDns.Text = string.Empty;
// Payload Tab (HEX)
if (temp.PayloadHex != null)
{
DynamicByteProvider dynamicByteProvider = new DynamicByteProvider(temp.PayloadHex);
hexEvent.ByteProvider = dynamicByteProvider;
if (temp.TcpDstPort == 53 || temp.TcpSrcPort == 53 || temp.UdpDstPort == 53 || temp.UdpSrcPort == 53)
{
try
{
DnsMessage dm = ARSoft.Tools.Net.Dns.DnsMessage.Parse(temp.PayloadHex);
if (dm.Questions.Count > 0)
{
txtDns.Text = "Questions:" + Environment.NewLine;
foreach (ARSoft.Tools.Net.Dns.DnsQuestion q in dm.Questions)
{
txtDns.Text += q.ToString() + Environment.NewLine;
}
txtDns.Text += Environment.NewLine;
}
if (dm.AnswerRecords.Count > 0)
{
txtDns.Text += "Answers:" + Environment.NewLine;
foreach (ARSoft.Tools.Net.Dns.DnsRecordBase r in dm.AnswerRecords)
{
txtDns.Text += r.ToString() + Environment.NewLine;
}
txtDns.Text += Environment.NewLine;
}
if (dm.AuthorityRecords.Count > 0)
{
txtDns.Text += "Authority Records:" + Environment.NewLine;
foreach (ARSoft.Tools.Net.Dns.DnsRecordBase r in dm.AuthorityRecords)
{
txtDns.Text += r.ToString() + Environment.NewLine;
}
txtDns.Text += Environment.NewLine;
}
if (dm.AdditionalRecords.Count > 0)
{
txtDns.Text += "AdditionalRecords:" + Environment.NewLine;
foreach (ARSoft.Tools.Net.Dns.DnsRecordBase r in dm.AdditionalRecords)
{
txtDns.Text += r.ToString() + Environment.NewLine;
}
}
}
catch (Exception ex){}
}
}
else
{
DynamicByteProvider dynamicByteProvider = new DynamicByteProvider(new byte[] { });
hexEvent.ByteProvider = dynamicByteProvider;
}
// Payload Tab (ASCII)
txtPayloadAscii.Text = temp.PayloadAscii;
// Misc Tab
txtEventSid.Text = temp.Sid.ToString();
txtEventCid.Text = temp.Cid.ToString();
txtSensor.Text = temp.SensorName;
txtPriority.Text = temp.SigPriority.ToString();
// Acknowledgement Tab
using (NPoco.Database dbMySql = new NPoco.Database(Db.GetOpenMySqlConnection()))
{
string query = _sql.GetQuery(snorbert.Configs.Sql.Query.SQL_ACKNOWLEDGEMENT_DETAILS);
query = query.Replace("#WHERE#", @"WHERE acknowledgment.id = @0");
List<Acknowledgment> acknowledgment = dbMySql.Fetch<Acknowledgment>(query, new object[] { temp.AcknowledgmentId });
if (acknowledgment.Any())
{
txtAckInitials.Text = acknowledgment.First().Initials;
txtAckClassification.Text = acknowledgment.First().Description;
txtAckNotes.Text = acknowledgment.First().Notes;
txtAckTimestamp.Text = acknowledgment.First().Timestamp.ToString();
chkAckSuccessful.Checked = acknowledgment.First().Successful;
}
}
}
}
public CsvImporter()
{
_db = DB.Database.GetDatabase();
}