How do I do search and replace within a search and replace perl statement?

Question

I’m using bash shell with Perl v 5.18.2. I’m running this statement

perl -pi -e "s/([^,]+),([^,]+),([^,]+),(.*[^\n]+)\n/select '\$1', u.id, u.first_Name, u.last_name, u.url FROM user u, user_role ur where u.id = ur.user_id and lower(ur.role_id) = '\$2' and lower(u.first_name) = lower('\$3') and lower(u.last_name) = lower('\$4');/g" /tmp/users.csv 

But what I want to do is somehow before the “$3” and “$4” arguments are placed into the replace section of this perl statement, I want to escape any single quotes that might appear in them so that the SQL statements run properly (this will run on MySQL 5.5.18). How do I adjust the above so that when perl replaces the $3 and $4 arguments it replaces single quotes with escaped MySQL singe quotes?

Answer 1

perl -pi -e "sub R{(\$a=shift)=~s/'/''/g;\$a;} s/([^,]+),([^,]+),([^,]+),(.*[^\n]+)\n/select '\$1', u.id, u.first_Name, u.last_name, u.url FROM user u, user_role ur where u.id = ur.user_id and lower(ur.role_id) = '\$2' and lower(u.first_name) = lower('@{[R(\$3)]}') and lower(u.last_name) = lower('@{[R(\$4)]}');/g;" /tmp/users.csv

Perl transposes any string (including regular expression). And execute any code enclosed in @{[ code ]}.

Create function (R()) for escaping MySQL string and call it from replacement string as @{[ R($3) ]}.