Re: pattern matching and replacing

["Duncan Cross" <duncan.cross@...>]

On Tue, Jun 17, 2008 at 1:43 PM, daniel rahmeh <daniel.rahmeh@gmail.com> wrote:
> my original query is: Insert into sometable (username, password,
> phone) values ('test', 'tespasswd', '123456789')
>
> i want my query to be modified to: Insert into sometable (username,
> password, phone) values ('test', SHA1('tespasswd'), '123456789')
>
> i'm unable to find the correct pattern, i tried this:
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> if string.match(query, 'insert into users (username, pass, telephone)
> values ("%w+", "%w+", "%w+")') then
>
> query = string.gsub(query,'insert into users (username, password,
> telephone) values ('%w+', '%w+', '%w+')', 'insert into users
> (username, password, telephone) values ('%w+', SHA1('%w+'), '%w+')')
>
> end
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> obviously it's not the correct way to do it
>
> any help will be appreciated
>
> thank you
>
> note: i know it can be done on the DBMS but i'm testing the MySQL Proxy features
>

Hi,

This is just based on glancing at your code:
* The string.match condition on the if-block surrounding the gsub is
matching double-quote enclosed parameter values, whereas the gsub
inside is matching single-quoted, so it currently won't ever succeed.
You don't need it to be inside an if-block at all - if gsub doesn't
match anything it'll return the original string anyway.
* You need to %-escape the parentheses around the list of parameters
in the pattern (but not the replacement)
* Add parentheses around the %w+ captures in the pattern, and then
refer to them in the replacement using %1, %2, %3 etc.