/// <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 List <_Cep> FetchAll()
{
using (Database db = new NPoco.Database("Teste"))
{
return(db.Fetch <_Cep>());
}
}
/// <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);
}
/// <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);
}
/// <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>
///
/// </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 IEnumerable <IdentityObject> All()
{
return(db.Fetch <IdentityObject>(" "));
}
/// <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;
}
}
}
}
/// <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();
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="dateFrom"></param>
/// <param name="dateTo"></param>
/// <param name="text"></param>
public void ExportAcknowledgmentsFromToAll(string filePath,
string dateFrom,
string dateTo,
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.timestamp > @0
AND acknowledgment.timestamp < @1");
var data = db.Fetch <Dictionary <string, object> >(query, new object[] { dateFrom, dateTo });
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());
streamWriter.WriteLine("Initials: " + temp["initials"].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.timestamp > @0
AND acknowledgment.timestamp < @1");
var data = db.Fetch <Dictionary <string, object> >(query, new object[] { dateFrom, dateTo });
// Write out the file headers
csvWriter.WriteField("Sig Name");
csvWriter.WriteField("Sig SID");
csvWriter.WriteField("Sig GID");
csvWriter.WriteField("Initials");
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());
csvWriter.WriteField(temp["initials"].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();
}
/// <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();
}
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;
* }
* };
*/
}
public static async Task <IEnumerable <User> > FetchUsers( )
{
return(await Task.Run(() => {
return _db.Fetch <User>();
}));
}
public IEnumerable <CompositeKeyObject> All()
{
return(db.Fetch <CompositeKeyObject>(" "));
}
public IEnumerable <NoKeyNonDistinctObject> All()
{
return(db.Fetch <NoKeyNonDistinctObject>(" "));
}
public IEnumerable <ListObject> All()
{
return(db.Fetch <ListObject>(" "));
}
public IEnumerable <ObjectsWithCustomType> All()
{
return(db.Fetch <ObjectsWithCustomType>(" "));
}
public IEnumerable <KeyedIntObject> All()
{
return(db.Fetch <KeyedIntObject>(" "));
}
/// <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;
}
}
}
}
/// <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();
}
/// <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();
}