String Based Trigger in MYSQL. Is this possible??

Bawnmore

Hi all, any help appreciated here!

Im trying to create a triggered event in MYSQL based on an inputted column string. I may be confusing myself so i'll try explain that a bit better:

My first table has 2 columns, an auto-increment column to signify the entry ID and a data column. The data column contains some extracted network traffic information. I want to be able to flag rows which contain specific strings, which i must have to pre-specify, and copy this row to another table for reference.

Is this possible??

Thanks in advance for the help

Tom Dunne

Bawnmore wrote: »

I want to be able to flag rows which contain specific strings, which i must have to pre-specify, and copy this row to another table for reference.

Is this possible??

Put it this way - can you do what you want to do outside of a trigger?

i.e. can you code your logic? If you can, you can then simply put the code into a procedure, set up the trigger to fire upon insert, call the procedure, and you are sorted.

Funnily enough, I have spent this afternoon creating a load of triggers on an Oracle database.

Bawnmore

Tom Dunne wrote: »

Put it this way - can you do what you want to do outside of a trigger?

i.e. can you code your logic? If you can, you can then simply put the code into a procedure, set up the trigger to fire upon insert, call the procedure, and you are sorted.

Funnily enough, I have spent this afternoon creating a load of triggers on an Oracle database.

thanks very much for the fast reply this is actually my first trying to perform a database task like this so im pretty much lost on how to perform procedures etc. is it much of a task or is it straight forward enough??

Tom Dunne

Bawnmore wrote: »

thanks very much for the fast reply this is actually my first trying to perform a database task like this so im pretty much lost on how to perform procedures etc. is it much of a task or is it straight forward enough??

Ok, forget about procedures, triggers and databases for now.

Get down on paper what exaclty you want to do. Better still, post it here.

Once you know exactly what you want to do, the rest is fairly straight forward.

Bawnmore

OK perfect, i'll put it down on paper as best i can:

I've extracted specific strings from test packet traces i was collecting. They are all similar but only a few are interesting to me. Heres an example of one of the records:

Request: PRIVMSG #Test :[MAIN]: Password accepted.

All of the records begin with the word "Request", followed by a log of whatever action occurred.

Some of these logs contain specific strings within them which I basically want to flag for further inspection. For example, one of the strings I want to flag is ".login". This would be contained in a record such as this:

Response: :seanoconnell87!seanoconne@192.168.1.15 PRIVMSG #Test :.login merkas

When one of these records is inserted into the database, i want to flag this record, and copy it into another table for further inspection.

That's pretty much it! Any more information i'd need to give you a general idea???

Thanks again

Sean

Giblet

Basically you want an on insert trigger, and search the inserted column for the strng, if it exists, insert the data into your other table.

Tom Dunne

Right, look up MySQL string functions, specifically instr() and substr().

In your trigger/procedure, you are ultimately looking for something along the lines of:

IF substr(X,Y) IS NOT NULL (Y would be the string you are looking for)
THEN
INSERT INTO <other table>

I'll let you fill in the details.

Bawnmore

Tom Dunne wrote: »

Right, look up MySQL string functions, specifically instr() and substr().

In your trigger/procedure, you are ultimately looking for something along the lines of:

IF substr(X,Y) IS NOT NULL (Y would be the string you are looking for)
THEN
INSERT INTO <other table>

I'll let you fill in the details.

Thats great, i'll do some research and try to get it sorted this evening and if i've any trouble or questions i'll post back again!

Cheers lads

Bawnmore

Alright still having problems here so again any help is great

Have read up on triggers and procedures and tested calling a procedure from a trigger and all is fine. Having trouble writing the procedure to do what i want however! The following is what i'm trying and having not much luck so far:

create procedure test_proc
begin
if substr('hello',1) is not null,
then insert into testtable2 (data2) values(data);
end

testtable2 is the name of the table I want to insert into, data is the name of the field being searched and data2 is the field in testtable2 i want to insert matches into.

I'm not sure if i'm so far away it's never going to happen or so close I can taste it

Again, any help is really appreciated!!

Tom Dunne

Here's what needs to happen:

You have an Insert Trigger which calls a procedure.

You need to pass the contents of the inserted field to your procedure.

You then need to search this string for the string you are looking for.

If you find what you are looking for, insert into new table.

Otherwise, do nothing and return.

Bawnmore

First of all, thanks very much for the quick reply guys, its a huge help.

Ok, had a go at what was suggested and it worked a treat, trigger called procedure, and everything was running smoothly! However I decided that PHP may be able to do everything I need in a less messy process so went with that - new problem!

At the moment heres what i have:

PHP code which takes an input text file of records, which are inserted into a database, when one of the records contain a string (checked from an array of possible keyphrases), copies this record into another table. All of that part is working perfectly.

What i'm trying to do now is to call a shell script at the beginning of the code which runs, creates the text file of records needed, and have the php code read these records into the database. Heres the code before I go any further:

<?php
//creating keywords array
// read the stings from a db
// put them into an array
$keywords=array(".login",".get",".capture",);

exec("/sda1/var/www/html/command");
sleep(15);

$lines = file('http://localhost/data.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$conn=mysql_connect('localhost','root','') or die("could not connect");
$rs=mysql_select_db('errorcheck',$conn) or die ('could not select db');
foreach ($lines as $line_num => $line){
$line=rtrim($line,"\r\n");
//echo ($line);
$sql="insert into packetlog (data) values (\"$line\");";
//echo("<br>".$sql."<br>");
$rs=mysql_query($sql,$conn) or die ("mysql error<br>$sql<br>".mysql_error());
// find the interesting ones
// for each element in array
foreach ($keywords as $i => $keyword){
if (strpos($line,$keyword) !== false){
//echo("found<br>");
$sql="insert into flaggedlog (data) values (\"$line\");";
//echo("<br>".$sql."<br>");
$rs=mysql_query($sql,$conn) or die ("mysql error<br>$sql<br>".mysql_error());

}
}
}

?>

When i run this, the bottom part of the code works, so i'm certain that the problem must be with the "exec" command as the script doesnt run and create the file needed. I tried using a simple script echoing hello instead of the other script and also got no output so it must be it!

Any help at all is really appreciated as i'm awfully short on time
thanks again in advance,
Sean

Bawnmore

Halt your efforts!! Got it sorted, it was just a bad directory name calling the script! Didnt need the sda1!

Thanks for all the help guys