0

This related question asks about using a parameterized query when using the commandline mysql tool in bash. However, it seems that the top answer is still vulnerable to injection (eg ; DROP TABLE user; --). While the answer certainly does address the question of how to pass in variables at all, it does not address the question of how to do it using parameterized queries.

My question: Does the linked accepted answer in the linked question provide protection against SQL injection, and have all the useful protections of paramterization? If so, why? If not, how can I securely use a parameterized query from the MySQL command line tool?

Note: Technically I am running mysql Ver 15.1 Distrib 10.3.13-MariaDB.

DreamConspiracy
  • 343
  • 2
  • 11

2 Answers2

4

Common practice for customer-facing applications is to have an API-endpoint for each database query, which will require user authentication. The API server will then validate the input while formatting the query.

Directly exposing bash on server is never a good idea. Besides SQL injection, other much worse situations, like ; scp ~/.ssh/id_rsa my_proxy ;, can easily happen.

It appears that security is not OP's primary concern based on the comments below. Rather, the main focus is generating valid queries.

For that, the simplest solution is to perhaps use existing libraries, and let them handle the formatting. For example, in Python there is

https://dev.mysql.com/doc/connector-python/en/

Usually insertion should be done in batch for efficiency. But if preferred, you can write a script for inserting a row like

python3 tableX_insert.py --field1 value1 --field2 value2

I am sure in other languages similar modules for DB conn and cursor exist. Any effort to do the same with raw bash command line is re-inventing wheels.

Pik-Mai Hui
  • 311
  • 1
  • 7
  • This is not a user-facing script. In the scenario that I have it turns out that there's a workaround for this anyway. Regardless, this question needs an answer. In particular, there are reasons to use parameterized queries that are not security related (separating data and instructions is very good practice). There was a very good post written about this on some other question on this site, but I can't find it right now. – DreamConspiracy Mar 27 '19 at 10:39
  • Perhaps you can explain more details on your question. I do not see why security is an issue when the script is not for costumer, because users who can enter the server are allowed to be there. I think their queries shouldn't be considered "injection". – Pik-Mai Hui Mar 27 '19 at 10:46
  • I have edited the question to remove the focus on security. See [here](https://phpdelusions.net/sql_injection#prepared) and the answer by the same author [here](https://stackoverflow.com/a/8255054/4509631). But to summarize the issue: I want ``O'Connolly`` to be a valid input that does not break the query or have adverse sideeffects. – DreamConspiracy Mar 27 '19 at 10:53
  • this is a much better idea that I had not considered. I have upvoted, but will leave it unaccepted in hopes that a better answer comes up (I'd be very shocked if there's no way to do this from ``mysql``). Regardless, thank! – DreamConspiracy Mar 27 '19 at 11:06
2

You can run PREPARE and EXECUTE in an SQL script to do parameterized statements, but then the tricky part for a bash script is to get values assigned to session variables in the SQL script without introducing an SQL injection vulnerability.

I mean you can do this:

myshellvar=1234
mysql -e "set @myvar = $myshellvar ; prepare stmt from 'select ?'; execute stmt using @myvar"

+------+
| ?    |
+------+
| 1234 |
+------+

But that's still vulnerable to SQL injection, because $myshellvar could contain troublesome content.

I did this experiment:

echo "O'Reilly" > /tmp/name

mysql -e "set @myvar = replace(load_file('/tmp/name'), '\n', ''); prepare stmt from 'select ?'; execute stmt using @myvar"
+----------+
| ?        |
+----------+
| O'Reilly |
+----------+

That's a safe way to ensure the content doesn't cause SQL injection, but you'd need a MySQL instance that is configured to allow load_file(), and it seems like a lot of work because you'd need to create a separate file for each variable you want to load.

I agree with @PMHui's answer that you should use some other scripting language if you want to write parameterized SQL queries conveniently.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you for this as well. This is what I was looking for. – DreamConspiracy Mar 28 '19 at 14:21
  • I want to be clear that this was a proof of concept only, and I wouldn't use this solution. – Bill Karwin Mar 28 '19 at 14:27
  • I didn't know you could use these statements outside stored procedures. – Barmar Jan 03 '22 at 18:50
  • @Barmar, Yes, the statements that work only in stored routines are documented as in the section [Compound Statement Syntax](https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html) in the manual. PREPARE/EXECUTE are in the section preceding it. – Bill Karwin Jan 03 '22 at 18:54