Update Query throwing Error in Command Line Bash only-CodePudding

I have the following query that I try to execute from a bash file:

echo 'update LOCKSTATUS'
if ! mysql -h site.com -u user -pPassword -D db -e "UPDATE tblcustomfieldsvalues a
JOIN
  (SELECT tblclients.userid,
          tblclients.company,
          tblcustomfieldsvalues.`value`,
          CoreUsers.`LOCKED_STATUS`,
          tblcustomfieldsvalues.id
   FROM tblclients
   LEFT JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblclients.userid
   AND tblcustomfieldsvalues.fieldto = 'customers'
   AND tblcustomfieldsvalues.fieldid = 16
   LEFT JOIN CoreUsers ON CoreUsers.USERID = tblclients.company) b
SET a.`value` = b.`LOCKED_STATUS`
WHERE a.id = b.id
  AND b.`LOCKED_STATUS` IS NOT NULL;"; then
  echo "Error: LOCK STATUS"
  exit 1
fi

this query works perfectly when executed through my mysql client. I wrapped some fields that could be problematic between `` but still, the bash file return an error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
          CoreUsers.,
          tblcustomfieldsvalues.id
   FROM tblclients
...' at line 5

CodePudding user response：

When you use double-quoted strings in Bash, it enables command expansion, so back-ticks run a command and substitute the stdout of that command.

For example:

% echo "hello `date` world"
hello Wed Feb  1 15:15:13 PST 2023 world

In your case, you used back-ticks within a Bash double-quoted string, so it's going to try to run the command value and the command LOCKED_STATUS. Probably neither of these are valid commands on your system, so their stdout is empty.

Thus your SQL becomes:

...
SELECT tblclients.userid,
      tblclients.company,
      tblcustomfieldsvalues.,
      CoreUsers.,
      tblcustomfieldsvalues.id
...

This is a syntax error, because you have no column names after the . in those two lines.

You don't need to use back-ticks anyway around the column names. You would need to use back-ticks only if the columns conflict with reserved keywords, or if they contain punctuation or whitespace or something that makes them nonstandard identifiers. But value and LOCKED_STATUS are not reserved words.

CodePudding user response：

There's an existing answer that describes the answer, but doesn't suggest how to fix it in the general case (which might include cases where you need backticks). Here's a suggested fix that lets you keep your query as it's already written:

query=$(cat <<'EOF'
UPDATE tblcustomfieldsvalues a
JOIN
  (SELECT tblclients.userid,
          tblclients.company,
          tblcustomfieldsvalues.`value`,
          CoreUsers.`LOCKED_STATUS`,
          tblcustomfieldsvalues.id
   FROM tblclients
   LEFT JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblclients.userid
   AND tblcustomfieldsvalues.fieldto = 'customers'
   AND tblcustomfieldsvalues.fieldid = 16
   LEFT JOIN CoreUsers ON CoreUsers.USERID = tblclients.company) b
SET a.`value` = b.`LOCKED_STATUS`
WHERE a.id = b.id
  AND b.`LOCKED_STATUS` IS NOT NULL;
EOF
)

echo 'update LOCKSTATUS'
if ! mysql -h site.com -u user -pPassword -D db -e "$query"; then
  echo "Error: LOCK STATUS"
  exit 1
fi

This is because a quoted heredoc (as created by <<'EOF' -- note the quotes, they're important) keeps everything until the next line with the sigil (EOF in this case; you can use other strings if appropriate) completely unmodified.