Replace string in bash-CodePudding

I have a sql file with over 1000 lines and the content is similar at this:

CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*K5OI8YF05MGAOO3GWZIDDA9JRZ1CHLLZM2D1WA86' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user2'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8SR9KK66KDC39QO5ZLX1POZQHKG5GWJP164L5HFF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user3'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*MNVDTUY6KAN61R653AKEH6CUFQVIGNI11LQUQ2C9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user4'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*K5OI8YF05MGAOO3GWZIDDA9JRZ1CHLLZM2D1WA86' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user5'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*MNVDTUY6KAN61R653AKEH6CUFQVIGNI11LQUQ2C9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
...
CREATE USER 'user1000'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8SR9KK66KDC39QO5ZLX1POZQHKG5GWJP164L5HFF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

and I need modify this file to replace the string that corresponds to the password to this:

CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'test' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user2'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'test' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user3'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'test' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user4'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'test' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user5'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'test' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
...
CREATE USER 'user1000'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'test' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

I tryed process this using sed, but I don´t have sucess.

Any suggestion?

Best regards

CodePudding user response：

If you want to replace all passwords with the same string using sed, you can use capture groups and backreferences:

sed -E "s/(AS )(')(.*)(')/\1\2$pass\4/g" in.txt
        s/                                          #substitute
          (AS )                                     #capture group 1, match "AS "
               (')                                  #group 2, match "'"
                  (.*)                              #group 3, match and character 0-infinity times
                      (')                           #group 4, match "'"

                         /                          #replace with

                          \1                        #backreference to group 1
                            \2                      #backreference to group 2
                              $pass                 #variable containing your new password
                                   \4               #backreference to group 4
                                     /g             #global modifier
                                         in.txt     #input file

Note that you need to use double quotes for the sed expression to be able to match the single quotes your password is in.

Example:

╰─$ cat in.txt       
CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*K5OI8YF05MGAOO3GWZIDDA9JRZ1CHLLZM2D1WA86' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user2'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8SR9KK66KDC39QO5ZLX1POZQHKG5GWJP164L5HFF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user3'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*MNVDTUY6KAN61R653AKEH6CUFQVIGNI11LQUQ2C9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user4'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*K5OI8YF05MGAOO3GWZIDDA9JRZ1CHLLZM2D1WA86' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user5'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*MNVDTUY6KAN61R653AKEH6CUFQVIGNI11LQUQ2C9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
...
CREATE USER 'user1000'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8SR9KK66KDC39QO5ZLX1POZQHKG5GWJP164L5HFF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
╭─user@machine ~/test/1 ‹master› 
╰─$ pass=asdf
╭─user@machine ~/test/1 ‹master› 
╰─$ sed -E "s/(AS )(')(.*)(')/\1\2$pass\4/g" in.txt 
CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'asdf' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user2'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'asdf' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user3'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'asdf' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user4'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'asdf' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
CREATE USER 'user5'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'asdf' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
...
CREATE USER 'user1000'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'asdf' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

CodePudding user response：

awk -v a="'newpass'" '{$8=a;print}' file

you can try -v Refer to the following link

https://unix.stackexchange.com/questions/172498/using-variable-with-awk-v-in-a-shell-script

CodePudding user response：

If the lines are all like that, you could read each line individually and then change the password as you wish. Something like this could potentially work:

cat file | while read -r line; do
       temp=$(echo $line | awk '{print $8}') #This is the password item
       npass='mypass'
        
       sed -i 's/'"$temp"'/'"$npass"'/' ./file
done

Using this, I was able to make changes to that file with no problem.