Forum Discussion

luojichen_22420's avatar
luojichen_22420
Icon for Nimbostratus rankNimbostratus
Mar 01, 2016

A question about "use irules to filter the TCP payload 's keywords"

DEAR ALL

 

I want to use irules to filter the KEYWword “select” in a oracle TNS query packet. Here is the irules， but it does not operate .After use the Irule in VirtualServer, I still can use “select” to query the SQL. Can anyone help me ？ thanks

 

when CLIENT_ACCEPTED {

 

TCP::collect

 

}

 

when CLIENT_DATA {

 

if { [TCP::payload 200] contains "select" } { reject }

 

TCP::release }

 

application delivery
iRules

8 Replies

Sort By
  • Josiah_39459's avatar
    Josiah_39459
    Historic F5 Account
    Mar 01, 2016
    A good troubleshooting step would be to add logging and make sure the irule is behaving the way you expect it to. For example you could put your tcp payload in a variable and log it before checking its contents.
  • Kai_Wilke's avatar
    Kai_Wilke
    Icon for MVP rankMVP
    Mar 01, 2016

    Hi Luojichen,

    you provided iRule inspects just the first TCP-Datagram of a TCP-Session. In addition to that it would just blocks "select" but not "SELECT" nor "SeLeCt". You may try the iRule below to inspect the entire TCP-Session and to negate the CASE of the SQL statements. 

    when CLIENT_ACCEPTED {
    TCP::collect 
}
when CLIENT_DATA {
    if { [string tolower [TCP::payload 200]] contains "select" } then { 
        reject 
    } 
    TCP::release
    TCP::collect
}

    Note: Include some log statements as recommended by Josiah. It would help you to see whats going on on the wire.

    Cheers, Kai

  • Vernon_97235's avatar
    Vernon_97235
    Historic F5 Account
    Mar 01, 2016

    When you simply say 

    TCP::collect
    you are more-or-less instructing BIG-IP to collect one segment into the 
    TCP::payload
    buffer before firing the CLIENT_DATA event. When you subsequently say 
    TCP::payload 200
    , that means return the payload buffer up to 200 bytes. If the first segment contains fewer than 200 bytes, it'll return whatever the segment length is. On the other hand, if you say 
    TCP::collect 200
    , the BIG-IP will attempt to collect segments until it reaches 200 bytes (though it may exceed that amount if segment boundaries don't align that way, and it may have less data if the connection terminates before 200 bytes are received).

    If you are absolutely certain that the select keyword will occur within the first 200 bytes:

    when CLIENT_ACCEPTED {
    TCP::collect 200
}

when CLIENT_DATA {
    if { [TCP::payload 200] contains "select" } {
        log local0. "Found select keyword..."
        reject
    }
    else {
        log local0. "Did not find select keyword..."
    }
    
    TCP::release
}

    The logging is there just for troubleshooting. Keep in mind, as well, that the 

    contains
    operator will only match if the word is ASCII (or UTF-8, because the codepoints are in the ASCII range) encoded.

    • luojichen_22420's avatar
      luojichen_22420
      Icon for Nimbostratus rankNimbostratus
      Mar 02, 2016
      Thankyou very much！ Maybe it is ASCII。 I can filter it use Wireshark filter < tcp contains ” select“ > HOW can I filter it in irules ?
  • VernonWells's avatar
    VernonWells
    Icon for Employee rankEmployee
    Mar 01, 2016

    When you simply say 

    TCP::collect
    you are more-or-less instructing BIG-IP to collect one segment into the 
    TCP::payload
    buffer before firing the CLIENT_DATA event. When you subsequently say 
    TCP::payload 200
    , that means return the payload buffer up to 200 bytes. If the first segment contains fewer than 200 bytes, it'll return whatever the segment length is. On the other hand, if you say 
    TCP::collect 200
    , the BIG-IP will attempt to collect segments until it reaches 200 bytes (though it may exceed that amount if segment boundaries don't align that way, and it may have less data if the connection terminates before 200 bytes are received).

    If you are absolutely certain that the select keyword will occur within the first 200 bytes:

    when CLIENT_ACCEPTED {
    TCP::collect 200
}

when CLIENT_DATA {
    if { [TCP::payload 200] contains "select" } {
        log local0. "Found select keyword..."
        reject
    }
    else {
        log local0. "Did not find select keyword..."
    }
    
    TCP::release
}

    The logging is there just for troubleshooting. Keep in mind, as well, that the 

    contains
    operator will only match if the word is ASCII (or UTF-8, because the codepoints are in the ASCII range) encoded.

    • luojichen_22420's avatar
      luojichen_22420
      Icon for Nimbostratus rankNimbostratus
      Mar 02, 2016
      Thankyou very much！ Maybe it is ASCII。 I can filter it use Wireshark filter < tcp contains ” select“ > HOW can I filter it in irules ?
  • Kai_Wilke's avatar
    Kai_Wilke
    Icon for MVP rankMVP
    Mar 02, 2016

    Hi Luojichen,

     

    If the HEX stream of wireshark contains readable SQL statements then [TCP::payload] could match the content. For further troubleshooting I would recommend to use the following code...

     

    when CLIENT_ACCEPTED {
    if { not ([IP::client_addr] contains 1.1.1.1) } then { return }
    TCP::collect
}
when CLIENT_DATA {
    log local0.debug "TSN Data: [TCP::payload]"
    if { [string tolower [TCP::payload]] contains "select" } then {
        log local0.debug "Request is a \"select\" statement."
        reject
    }     
    TCP::release    
    TCP::collect
}

    Note: Change the 1.1.1.1 to reflect the IP of your Client PC running the SQL query.

     

    Cheers, Kai

     

  • Kai_Wilke's avatar
    Kai_Wilke
    Icon for MVP rankMVP
    Mar 03, 2016

    Hi Luojichen,

     

    I don't have experiences with Oracle SQL. But its very unlikely that you can interupt a TCP-Session midstream and then forward just a single TCP-Segment (containing a specific query) to a different pool/member.

     

    I strongly believe it is required to capture the initial client authentication packet exchange and then replay those packets for every Oracle pool reselection, to initially authenticate the backend TCP connection (aka. establishing the TCP session based authentication) and to finaly be able to forward the given SQL query over the "now" trusted connection.

     

    Note: The outlined TCP-session based authentication bahavior is as least valid for MSSQL, MYSQL and LDAP databases to increase the performance of keep-alive connections. If Oracle wouldn't do that too, then it would be required to authenticate each single SQL-Query which would in turn degrate the overall performance to a great extent.

     

    Cheers, Kai