289 lines
8.8 KiB
C#
289 lines
8.8 KiB
C#
using Npgsql;
|
|
using NpgsqlTypes;
|
|
using skyscraper5.src.Skyscraper.Scraper.Dns;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Net;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace skyscraper5.Data.PostgreSql
|
|
{
|
|
public partial class PostgresqlDataStore
|
|
{
|
|
public long DnsCountA()
|
|
{
|
|
throw new NotImplementedException();
|
|
}
|
|
|
|
private uint dnsIpMisses;
|
|
public string DnsIpToName(IPAddress source)
|
|
{
|
|
string result = source.ToString();
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
NpgsqlCommand command = connection.CreateCommand();
|
|
command.CommandText =
|
|
"SELECT domain_name\r\n" +
|
|
"FROM dns_ips ips\r\n" +
|
|
"LEFT JOIN dns_records records on ips.ip_id = records.ip_id\r\n" +
|
|
"LEFT JOIN dns_domains domains on records.domain_id = domains.domain_id\r\n" +
|
|
"WHERE ips.ip_address = @ia\r\n" +
|
|
"AND (records.record_type = 1 OR records.record_type = 28)";
|
|
command.Parameters.AddWithValue("@ia", source);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
if (dataReader.Read())
|
|
result = dataReader.GetString(0);
|
|
else
|
|
dnsIpMisses++;
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
connection.Close();
|
|
}
|
|
return result;
|
|
}
|
|
|
|
|
|
public bool TestForIp(IPAddress iP)
|
|
{
|
|
bool result;
|
|
using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
|
|
{
|
|
connection.Open();
|
|
result = TestForIp(connection, iP);
|
|
connection.Close();
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public void RememberDnsRecord(DnsRecord record)
|
|
{
|
|
if (record.ToString().Contains("\0"))
|
|
return;
|
|
|
|
EnqueueTask(x => {
|
|
RememberDnsRecordEx(x, record);
|
|
}
|
|
);
|
|
}
|
|
|
|
private uint deduplicated;
|
|
private void RememberDnsRecordEx(NpgsqlConnection x, DnsRecord record)
|
|
{
|
|
if (record.Domain.ToString().Contains("\0"))
|
|
{
|
|
return;
|
|
}
|
|
|
|
long domainId;
|
|
if (TestForDomainName(x,record.Domain))
|
|
domainId = SelectDomainId(x,record);
|
|
else
|
|
domainId = InsertDomainName(x,record);
|
|
|
|
bool isIp = record.RecordType.Item2.Equals("A") || record.RecordType.Item2.Equals("AAAA");
|
|
long? ipId = null;
|
|
if (isIp)
|
|
{
|
|
if (TestForIp(x,record.IP))
|
|
ipId = SelectIp(x,record.IP);
|
|
else
|
|
ipId = InsertIp(x,record.IP);
|
|
}
|
|
|
|
if (!TestForDnsRecord(x,record, domainId, ipId))
|
|
InsertDnsRecord(x,record, domainId, ipId);
|
|
else
|
|
deduplicated++;
|
|
|
|
if (isIp)
|
|
{
|
|
long vhostId;
|
|
if (TestForVhost(x, record.Domain))
|
|
vhostId = SelectVhostId(x, record.Domain);
|
|
else
|
|
vhostId = InsertVhost(x, record.Domain);
|
|
|
|
if (!TestForIpVHostMapping(x, ipId.Value, vhostId))
|
|
InsertIpVHostMapping(x, ipId.Value, vhostId);
|
|
}
|
|
}
|
|
|
|
private void InsertIpVHostMapping(NpgsqlConnection x, long ipId, long vhostId)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "INSERT INTO dns_ip_vhosts (ip_id, vhost_id) VALUES (@iid,@vid)";
|
|
command.Parameters.AddWithValue("@iid", ipId);
|
|
command.Parameters.AddWithValue("@vid", vhostId);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
private bool TestForIpVHostMapping(NpgsqlConnection x, long ipId, long vhostId)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM dns_ip_vhosts WHERE ip_id = @iid AND vhost_id = @vid";
|
|
command.Parameters.AddWithValue("@iid", ipId);
|
|
command.Parameters.AddWithValue("@vid", vhostId);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private long InsertVhost(NpgsqlConnection x, string domain)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "INSERT INTO dns_vhosts (vhost_name) VALUES (@vn) RETURNING vhost_id";
|
|
command.Parameters.AddWithValue("@vn", domain);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
long result = dataReader.GetInt64(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private long SelectVhostId(NpgsqlConnection x, string domain)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "SELECT vhost_id FROM dns_vhosts WHERE vhost_name = @vn";
|
|
command.Parameters.AddWithValue("@vn", domain);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
long result = dataReader.GetInt64(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private bool TestForVhost(NpgsqlConnection x, string domain)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM dns_vhosts WHERE vhost_name = @vn";
|
|
command.Parameters.AddWithValue("@vn", domain);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private void InsertDnsRecord(NpgsqlConnection x, DnsRecord record, long domainId, long? ipId)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "INSERT INTO dns_records (domain_id, record_type, ttl, resource_data, ip_id) VALUES (@did,@rt,@ttl,@rd,@iid)";
|
|
command.Parameters.AddWithValue("@did", domainId);
|
|
command.Parameters.AddWithValue("@rt", record.RecordType.Item1);
|
|
command.Parameters.AddWithValue("@ttl", (int)record.TTL.TotalSeconds);
|
|
command.Parameters.AddWithValue("@rd", record.ResourceData);
|
|
if (ipId.HasValue)
|
|
command.Parameters.AddWithValue("@iid", NpgsqlDbType.Bigint, ipId);
|
|
else
|
|
command.Parameters.AddWithValue("@iid", NpgsqlDbType.Bigint, DBNull.Value);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
private bool TestForDnsRecord(NpgsqlConnection x, DnsRecord record, long domainId, long? ipId)
|
|
{
|
|
record.ResourceData = record.ResourceData.Trim('\0');
|
|
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "SELECT dateadded FROM dns_records WHERE domain_id = @did AND record_type = @rt AND resource_data = @rd";
|
|
command.Parameters.AddWithValue("@did", domainId);
|
|
command.Parameters.AddWithValue("@rt", record.RecordType.Item1);
|
|
command.Parameters.AddWithValue("@rd", record.ResourceData);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
bool result = dataReader.Read();
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private long? InsertIp(NpgsqlConnection x, IPAddress iP)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "INSERT INTO dns_ips (ip_address) VALUES (@ia) RETURNING ip_id";
|
|
command.Parameters.AddWithValue("@ia", iP);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
long result = dataReader.GetInt64(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private long? SelectIp(NpgsqlConnection x, IPAddress iP)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "SELECT ip_id FROM dns_ips WHERE ip_address = @ia";
|
|
command.Parameters.AddWithValue("@ia", iP);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
long result = dataReader.GetInt64(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private bool TestForIp(NpgsqlConnection x, IPAddress iP)
|
|
{
|
|
bool result;
|
|
using (NpgsqlCommand cmd = x.CreateCommand())
|
|
{
|
|
cmd.CommandText = "SELECT dateadded FROM dns_ips WHERE ip_address = @ip";
|
|
cmd.Parameters.AddWithValue("@ip", iP);
|
|
using (NpgsqlDataReader reader = cmd.ExecuteReader())
|
|
{
|
|
result = reader.Read();
|
|
reader.Close();
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
|
private long InsertDomainName(NpgsqlConnection x, DnsRecord record)
|
|
{
|
|
long result;
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "INSERT INTO dns_domains (domain_name) VALUES (@name) RETURNING domain_id";
|
|
command.Parameters.AddWithValue("@name", record.Domain);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
result = dataReader.GetInt64(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private long SelectDomainId(NpgsqlConnection x, DnsRecord record)
|
|
{
|
|
NpgsqlCommand command = x.CreateCommand();
|
|
command.CommandText = "SELECT domain_id FROM dns_domains WHERE domain_name = @name";
|
|
command.Parameters.AddWithValue("@name", record.Domain);
|
|
NpgsqlDataReader dataReader = command.ExecuteReader();
|
|
dataReader.Read();
|
|
long result = dataReader.GetInt64(0);
|
|
dataReader.Close();
|
|
command.Dispose();
|
|
return result;
|
|
}
|
|
|
|
private bool TestForDomainName(NpgsqlConnection x, string domain)
|
|
{
|
|
bool result;
|
|
using (NpgsqlCommand cmd = x.CreateCommand())
|
|
{
|
|
cmd.CommandText = "SELECT dateadded FROM dns_domains WHERE domain_name = @name";
|
|
cmd.Parameters.AddWithValue("@name", domain);
|
|
NpgsqlDataReader npgsqlDataReader = cmd.ExecuteReader();
|
|
result = npgsqlDataReader.Read();
|
|
npgsqlDataReader.Close();
|
|
}
|
|
return result;
|
|
}
|
|
}
|
|
}
|