Forum Discussion

Simon_Knox_1115's avatar
Simon_Knox_1115
Icon for Nimbostratus rankNimbostratus
Dec 12, 2005

TCP::payload replace and MSSQL

Hello,

 

 

I'm trying to figure out away to change the database value within a SQL connection string as it passes through the Big-Ip on the way to the DB server.

 

 

I'm guessing that I need to do a "TCP::collect" and then search for a specific string (the db name the client is using ) then use "TCP::payload replace" to change it to the Db name I want to use.

 

 

Unfortunatly I have no idea how to approach this. Can anyone help? or is there a better way to do this?

 

 

Cheers

 

Simon
application delivery
dev
devops
iRules
  • unRuleY_95363's avatar
    unRuleY_95363
    Historic F5 Account
    Dec 12, 2005
    There should be some examples already in the forum for doing search/replace within a TCP stream. Try seaching for the keywords you have a hunch would be involved, like: "TCP::payload replace" or "TCP::collect". I seem to remember something out there that should be fairly close. You should also check the CodeShare and Docs sections as Colin and Joe regularly publish examples there as well.
  • Simon_Knox_1115's avatar
    Simon_Knox_1115
    Icon for Nimbostratus rankNimbostratus
    Dec 13, 2005
    Hello,

    I've done some digging around and come up with this (which might be a bit simplistic and optomistic):

    
when CLIENT_ACCEPTED {
            TCP::collect 300
        }
when CLIENT_DATA {
        if { [TCP::payload] contains "dbafa" } {
           TCP::payload replace 0 0 "master"
           pool SQL-WiP
        }
}

    What I'm really confused with is how to know what he offset and length variables should be for the TCP::payload replace command. I've done some packet inspection and the database name seems to be right at the very end of the data.

    Can anyone help?

    Simon
  • bl0ndie_127134's avatar
    bl0ndie_127134
    Historic F5 Account
    Dec 14, 2005
    Simon, if you are trying to modify the TDS stream, you will want to be careful how you rewrite the data as a lot of its fields are length delineated. In another words, there may be offset and length entries that need to be modified along with the field values. I would suggest checking out http://www.freetds.org/tds.htmlexamples for more information on the wire format of TDS.

     

     

    Hope that helps.
  • unRuleY_95363's avatar
    unRuleY_95363
    Historic F5 Account
    Dec 14, 2005
    You will likely want to use the "string first" command instead of contains. "string first" will return an index of where the string was matched. You can then use that index in the TCP::payload replace command to replace the correct location.

     

     

    And as bl0ndie indicated, you may have to replace a little more than just the string.