Some help neede

[Mikheil]

I haven't done any programming for some time, but I've got a project that I need to update, because the GeoIP download is way out of date..

It reads Blocked IPAddresses from my Mail Log, resulting from the Blacklist filters. I decided that instead of just dispplaying them, I want to add them to a MySQL database.

It works perfectly BUT - I can get multiple entries for the same IPAddress. Some of these spammer morons can attempt to send hundreds of attempts from the same IP.

Here's my code to update the database :

procedure updatedb;
var
Csql : string;
begin
Form1.ZQuery1.Active;
Form1.ZQuery1.SQL.Text := 'select * from SPAMMER';
Form1.ZQuery1.Open;
Form1.ZQuery1.Append;
Form1.ZQuery1.FieldByName('TDay').Value := Date;
Form1.ZQuery1.FieldByName('IP').Value := IP;
Form1.ZQuery1.FieldByName('CCode').Value := CoCode;
Form1.ZQuery1.FieldByName('Country').Value := Cont;
Form1.ZQuery1.FieldByName('City').Value := Cit;
Form1.ZQuery1.FieldByName('Zip').Value := PCode;
Form1.ZQuery1.FieldByName('Region').Value := Regn;
Form1.ZQuery1.FieldByName('Latitude').Value := Latd;
Form1.ZQuery1.FieldByName('Longtitude').Value := Longd;
Form1.ZQuery1.FieldByName('ACode').Value := AreaC;
Form1.ZQuery1.FieldByName('ISP').Value := ISPn;
Form1.ZQuery1.Post;
end;

I tried making the IP a Primary Key, but I have no idea how to handle the exception and once I hit a duplicate, the program crashes.

As I said, it's been a while, takes time to pick up again.

Help would be much appreciated.

[marsupilami]

Hello,

if you only want to add IP addresses to a table in MySQL and not display them at the same time, you don't need to do the 'select * from ...'. In MySQL itr seems to make more sense to either use "on duplicate key update" or "replace into". I found that here:
https://thispointer.com/insert-into-a-m ... if-exists/

Code like this seems to be appropriate:

Code: Select all

procedure TForm1.OnFormCreate(Sender: TObject);
begin
  // here is other code...
  ZQuery1.SQL.Text := "insert into spammer (TDay, IP) values (:TDay, :IP) on duplicate key update TDay = :TDay"]
  // here is more other code...
end;

procedure TForm1.UpdateDB(Sender: TObject);
begin
  ZQuery1.ParamByName('TDay').Value := Date;
  ZQuery1.ParamByName('IP').Value := IP;
  ZQuery1.ExecSQL;
end;

This assumes that IP is the primary key of your spammer table. I left out some fields that are not necessary for the demonstration.

Best regards,

Jan

[Mikheil]

Thanks for that Jan, but this line doesn't seem to make sense. *I'm getting old (80 next month)

ZQuery1.SQL.Text := "insert into spammer (TDay, IP) values (:TDay, :IP) on duplicate key update TDay = :TDay"]

Why would I want to update TDay. If there is a duplicate, insert nothing at all. Below is how I parse the logfile

Procedure FindBlock(Str1, Str2: ANSIString);
var i : integer;
Begin
position := AnsiPos(Str1, Str2);
if position <> 0
then
begin
IPStr :='';
for i:= position+8 to position+22 do
begin
if Str2 in ['0'..'9', '.'] then
IPStr := IPStr + Str2
else
Break;
end;
Form1.CallGeoIPCity(IPStr);
Form1.CallGeoASN(IPStr);
Form1.Memo1.Lines.Add(CSVString);
// Send to CSV
If Form1.dbCSV.Checked = True then
Begin
Append(IPFile);
Writeln(IPFile, CSVString);
End;
// Send to MySQL
If Form1.cbMySQL.Checked = True then
Begin
Updatedb;
End;
End;
end;

Where would I put the line of code to ignore the duplicate IP's?

[marsupilami]

Why would I want to update TDay. If there is a duplicate, insert nothing at all. Below is how I parse the logfile

Because updating your record keeps you from getting an error. MySQL would simply overwrite the data in the record. So if you see the same IP address on two days you would have the latest day in the record. If you don't want to modify the record for some reason, you might want to check if the record for the IP address already exists and act accordingly. You could have another query where you do something like "select count(*) from spammer where IP = :IP" or something like this and then act on the number returned:

Code: Select all

procedure TForm1.OnFormCreate(Sender: TObject);
begin
  // here is other code...
  ZQuery1.SQL.Text := 'insert into spammer (TDay, IP) values (:TDay, :IP)';
  ZSearchQuery.SQL.Text := 'select count(*) from spammer where IP = :IP';
  // here is more other code...
end;

procedure TForm1.UpdateDB(Sender: TObject);
begin
  ZSearchQuery.Close;
  ZSearchQuery.ParamByName('IP').Value := IP;
  ZSearchQuery.Open;
  try
    if ZSearchQuery.Fields[0].AsInteger = 0 then begin
      ZQuery1.ParamByName('TDay').Value := Date;
      ZQuery1.ParamByName('IP').Value := IP;
      ZQuery1.ExecSQL;
    end;
  finally
    ZSearchQuery.Close;
  end;
end;

This approach has the drawback that it takes more round trips between the application and the server when inserting data into the table.

[Mikheil]

I'm sorry that it's taken me so long to reply, but I contracted either Salmonella or Campylobacter. Waste of time trying to get any help from doctors, they are ONLY interested in Covid. I'm almost recovered now, doctoring myself.

Whilst I got the idea of what you are trying to tell me, I'm afraid that the actual implementation went way over my head. It's been a very long time since I used Delphi, let alone databases and maybe I'm getting too old the learn quickly again!

I have two procedures involved as I showed. First is FindBlock. This picks up the IP from the Logfile and for each one it finds, it calls the second procedure, Updatedb.

As I understand this, I should be checking for an already existing key In the FindBlock procedure, either by checking it it already exists in the procedure, or by calling another procedure which returns true or false if the key exists and it it does, I will NOT try to execute Updatedb, just go to the next record, until a unique record is found.

As a matter of interest, yesterday I had the same spammer with the same IPAddress (which is blocked), making 123 attempts to send spam, so there are 123 records with the same IP, PLUS 37 others with different IP Addresses, some of them also duplicated between 2 and 15 times. Spam is getting completely out of hand.

To get back on topic, should I check in the FindBlock or rewrite it to :
1. first find each blocked IP
2. check if it already exists
3. if yes, skip to next Blocked IP, if not Updatedb

This makes sense to me. Would it be better to actually write a Function, returning True or False, if the record already exists or do the check inside the FindBlock procedure?

In both cases, the extra query to check if the record is unique will entail opening or closing or manipulating the database, or can I do the query to check without changing the state of the database?

Forgive me if I seem a bit slow, it's been a while and I'm still not feeling at my best.

[aehimself]

While your code works, I personally don't like it. If you can, avoid using global variables, the same what you want to do can be achieved with a simple parameter, like this:

Code: Select all

Procedure TForm1.UpdateDB(Const inIP: String);

For this to work you need to rewrite ReadBlock to return the extracted IP address:

Code: Select all

Function TForm1.ReadBlock: String;

Now, in your main method you simply can say:

Code: Select all

Procedure TForm1.FormCreate(Sender: TObject);
Var
 ip: String;
Begin
 ip := ReadBlock;
 UpdateDB(ip);
 // Or simply UpdateDB(ReadBlock);

If you want to avoid duplicates altogether, have a query with SELECT *. You can use this to search if an IP exists already and to update the DB:

Code: Select all

Procedure UpdateDB(Const inIP: String);
Begin
 If selectallquery.Locate('IP', inIP, []) Then Exit; // If a record was found with the incoming IP in the 'IP' field, do nothing

 selectallquery.Append;
 selectallquery.FieldByName('IP') := inIP;
 selectallquery.Post; // or .ApplyUpdates, depending on transactions and what you want to achieve
End;

If you let Zeos to do the hard work it will use parameters for all updates and inserts so you don't have to worry about that either; e.g. the above will be translated to INSERT INTO db.mytable (IP) VALUES (:p1), with p1 being inIP.