mysql programming question

[justDIY]

is anyone familiar with mysql's rendition of stored procs?  I think they claim to comply with some standard called SQL:2003, whatever that means.

What I would like to do is use some logic in the stored proc to build the WHERE clause for a select statement.  right now I'm doing simple if variable = 0 then SELECT blah WHERE blah=blah else SELECT... so I end up getting one statement the way I want it, and then copy and paste it into the rest of the conditions.  Problem being, if I change something in one statement, and forget to copy and paste it into the rest of the conditions, I get weird results.

I've tried putting the IF THEN ELSE block inside the select statement, but all I get are errors.

I must not be searching with the right keywords, as google is not much help.

Here is one of my SP's - the only thing that changes in each statement is the WHERE clause and the GROUP BY clause.  Just for the curious, in case it's not obvious, this code is for analyzing firewall logs.

Code:

if goodbad = 0 then
SELECT
MAX(ulog.`timestamp`) AS 'Time',
inet_ntoa(ip_saddr) AS 'Source_IP',
proto.protocol AS 'Protocol',
inet_ntoa(ip_daddr) AS 'Dest_IP',
ulogdb.ulog.tcp_dport AS 'tcp',
ulogdb.ulog.udp_dport AS 'udp',
ulogdb.ulog.oob_prefix AS 'Rule',
count(*) AS 'Con_Count'

FROM ulogdb.ulog
Left Join webfwlog.hostnames AS s_hostnames ON s_hostnames.ip_addr = ulogdb.ulog.ip_saddr
Left Join webfwlog.hostnames AS d_hostnames ON d_hostnames.ip_addr = ulogdb.ulog.ip_daddr
Left Join ulogdb.ip_protocols AS proto ON proto.ip_protocol = ulog.ip_protocol

WHERE
ulogdb.ulog.oob_in LIKE 'eth0' AND
ulog.oob_prefix LIKE '%ACCEPT%' AND /* accepted packets only */
ulogdb.ulog.`timestamp` >= SUBDATE(Now(), dayscount)

GROUP BY ip_saddr, tcp_dport, udp_dport, ip_daddr

ORDER BY
`Time` DESC;

elseif goodbad = 1 then
SELECT
ulog.`timestamp` AS 'Time',
inet_ntoa(ip_saddr) AS 'Source_IP',
proto.protocol AS 'Protocol',
inet_ntoa(ip_daddr) AS 'Dest_IP',
ulogdb.ulog.tcp_dport AS 'tcp',
ulogdb.ulog.udp_dport AS 'udp',
ulogdb.ulog.oob_prefix AS 'Rule',
count(*) AS 'Con_Count'

FROM ulogdb.ulog
Left Join webfwlog.hostnames AS s_hostnames ON s_hostnames.ip_addr = ulogdb.ulog.ip_saddr
Left Join webfwlog.hostnames AS d_hostnames ON d_hostnames.ip_addr = ulogdb.ulog.ip_daddr
Left Join ulogdb.ip_protocols AS proto ON proto.ip_protocol = ulog.ip_protocol

WHERE
ulogdb.ulog.oob_in LIKE 'eth0' AND
(ulog.oob_prefix LIKE '%DENY%' OR /* accepted packets only */
ulog.oob_prefix LIKE '%REJECT%') AND /* accepted packets only */
ulogdb.ulog.`timestamp` >= SUBDATE(Now(), dayscount)

GROUP BY ip_saddr, tcp_dport, udp_dport, ip_daddr

ORDER BY `Time` DESC;

else

SELECT
max(ulog.`timestamp`) AS 'Time',
inet_ntoa(ip_saddr) AS 'Source_IP',
proto.protocol AS 'Protocol',
inet_ntoa(ip_daddr) AS 'Dest_IP',
ulogdb.ulog.tcp_dport AS 'tcp',
ulogdb.ulog.udp_dport AS 'udp',
ulogdb.ulog.oob_prefix AS 'Rule',
count(*) AS 'Con_Count'

FROM ulogdb.ulog
Left Join webfwlog.hostnames AS s_hostnames ON s_hostnames.ip_addr = ulogdb.ulog.ip_saddr
Left Join webfwlog.hostnames AS d_hostnames ON d_hostnames.ip_addr = ulogdb.ulog.ip_daddr
Left Join ulogdb.ip_protocols AS proto ON proto.ip_protocol = ulog.ip_protocol

WHERE
ulogdb.ulog.oob_in LIKE 'eth0' AND
ulogdb.ulog.`timestamp` >= SUBTIME(Now(),' 12:00:00.00')

GROUP BY ip_saddr, tcp_dport, udp_dport, ip_daddr

ORDER BY `Time` DESC;
end if

[justDIY]

replying to myself here ... I found the answer is a bit different than I'd envisioned it, hence why I couldn't find anything on google.  once I found the right keywords to search, the answer was there!

the solution to my problem of building statements 'on the fly' was the PREPARE command - prepared statements. 

here is one of my SP's redone to support prepared statements:

Code:

BEGIN
DECLARE intdirection TINYTEXT;
DECLARE sourcer TINYTEXT;
DECLARE my_fields TEXT;
DECLARE my_where TEXT;
DECLARE my_group TEXT;
DECLARE my_ip TEXT;

SET my_fields = "CREATE TEMPORARY TABLE detail_results
TYPE = memory

SELECT
inet_ntoa(ulog.ip_saddr) AS 'Source_IP',
inet_ntoa(ulog.ip_daddr) AS 'Dest_IP',
count(*) as 'Con_Count',
tcp_services.service as 'tcp_service',
ulog.tcp_dport as 'tcp',
udp_services.service as 'udp_service',
ulog.udp_dport as 'udp',
min(ulog.`timestamp`) as 'first',
max(ulog.`timestamp`) as 'last',
ulog.oob_prefix as 'rule'

FROM ulogdb.ulog
Left Join ulogdb.services AS tcp_services ON tcp_services.port = ulogdb.ulog.tcp_dport
Left Join ulogdb.services AS udp_services ON udp_services.port = ulogdb.ulog.udp_dport ";

SET my_where = "WHERE ulog.`timestamp` >= SUBDATE(Now(), INTERVAL 24 HOUR) AND ";

SET my_group = "ip_saddr, tcp_dport, udp_dport, oob_prefix";

IF trafdir = 0 THEN
SET intdirection = "ulog.oob_out LIKE 'eth0' "; /* outbound traffic */
ELSE
SET intdirection = "ulog.oob_in LIKE 'eth0' "; /* inbound traffic */
END IF;

IF srcdest = 0 THEN
SET sourcer = "ip_saddr"; /* IP address is SOURCE */
ELSE
SET sourcer = "ip_daddr"; /* IP address is DEST */
END IF;

SET my_ip = CONCAT('inet_ntoa(',sourcer,') LIKE ''',ipaddr,''''); /* match source ip address */

/*SET @s = CONCAT(my_fields, my_where, intdirection, 'AND ', my_ip, ' GROUP BY ', sourcer, ', ', my_group);*/
SET @s = CONCAT(my_fields, my_where, intdirection, 'AND ', my_ip, ' GROUP BY ', my_group);

PREPARE stmt1 FROM @s;

DROP TEMPORARY TABLE IF EXISTS detail_results ;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

END