bash script concat() in prepared statement for mysql

Question

This gives a syntax error:

read -p "entry: " entry
sql="select concat('entry ', id) from mytbl where id = ?";
$mysql_conn "prepare stmnt from '${sql}'; set @id='${entry}';
 execute stmnt using @id;deallocate prepare stmnt";

It works if used directly in mysql, but not through a bash script. If only id is selected it works. But concat() throws it off. It will also work if a direct query is made without a prepared statement. What gives?

Also tried:

select concat('entry ',?)

and pass @id but it failed too.

katxarra · Accepted Answer · 2025-10-25 13:44:34Z

3

The issue is quote nesting conflict. When you write:

$mysql_conn "prepare stmnt from '${sql}'..."

And ${sql} contains 'entry ', the single quotes inside clash with the outer statement's quotes.

Best solutions:

Use double quotes in the PREPARE statement (Solution 1):
```
$mysql_conn "prepare stmnt from \"${sql}\"; ..."
```
Use a heredoc (Solution 2) - cleanest approach:

$mysql_conn <<EOF
prepare stmnt from "select concat('entry ', id) from mytbl where id = ?";
set @id='${entry}';
execute stmnt using @id;
deallocate prepare stmnt;
EOF

Escape the inner single quotes (Solution 3):

sql="select concat(\'entry \', id) from mytbl where id = ?"

answered Oct 25 at 13:44

katxarra

541 bronze badge

Sign up to request clarification or add additional context in comments.

6 Comments

Tim Biegeleisen Oct 25 at 13:50

This answer feels as though it were generated by AI.

masoudiofficial Oct 26 at 4:11

A fun fact : those who say AI shouldn't be used also shouldn't use documentation, like JavaScript, PHP, or any other language docs (unless the person using it has no idea what they want or what they're doing).

Tim Biegeleisen Oct 26 at 4:12

Not at all...the fidelity of most official docs is way higher than output from LLMs, which, as of 2025, can hallucinate and say things which aren't true.

bungee1980 Oct 25 at 13:53

makes perfect sense. thank you. i went with the escaped double quotes

katxarra Oct 25 at 13:54

Sure no problem =)

Léa Gris Oct 25 at 22:56

You still need to safe quote $entry bash side before inserting it into the SQL statement. Even with prepared statement, this is still unsafe in that it might still break the SQL syntax. I really still don't understand why the mysql shell command still is unable to transfer shell arguments to SQL variables. This would save so much headache sending SQL request fro the shell.

Collectives™ on Stack Overflow

bash script concat() in prepared statement for mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related