Path: csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: "J.O. Aho" <user@example.net>
Newsgroups: comp.databases.mysql
Subject: Re: From bash script (Debian Bookworm) login and query to MariaDB
Date: Wed, 1 Nov 2023 08:04:24 +0100
Lines: 50
Message-ID: <kqebjoFam3fU1@mid.individual.net>
References: <uhr78k$13p7r$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net +wIZqRVC/5ruKmZol8QxggzcjhYNBMIPO5i3U2NUXT4RxGGsT3
Cancel-Lock: sha1:tzaM4zqHUG3/LSPv62lZJNAX0hw= sha256:vuM6tyhM4Vep2sBBlYGBme5YhdZEjENlyfvlZ/ZESKA=
User-Agent: Mozilla Thunderbird
Content-Language: en-US-large
In-Reply-To: <uhr78k$13p7r$1@dont-email.me>
Xref: csiph.com comp.databases.mysql:7664

On 31/10/2023 16.42, ^Bart wrote:
> Hi guys,
> 
> I need to write in bash a script which do these things:
> 
> 1) Login to MariaDB;
> 2) Truncate a table to make it empty;
> 3) Import data from a file to this table.
> 
> I found this guide 
> https://notearena.com/lesson/how-to-access-databasemysql-mariadb-in-linux-shell-scripting/ and I'd like to know if it's the right way! :)
> 
> Now I do everything manually...

The main issue with that script is the base64 encoded password, from 
security standard point of view that is still as if the password was 
stored in plain text.

As Jerry pointed out, you can just do everything with a simple file with 
the sql commands and run it with the command line, but with the 
exception that I wouldn't use the password in the command line but let 
the mysql command for prompting me to enter the password, as otherwise 
you store the plain text password in bash history.

mysql -hhost -uuser -p < reload.sql

sure if you need to automate this like running cron job, then you will 
need to provide a password, then see to that the file is just readable 
by the user it's executed as and that user ain't someone that people 
just can login in with, don't forget that by default root will be able 
to read the file.

You may also consider to create a store procedure that does everything 
you need,

https://mariadb.com/kb/en/create-procedure/
https://mariadb.com/kb/en/stored-routine-privileges/
https://mariadb.com/kb/en/call/

then you don't have to have a script on each machine you may need to 
execute the truncation and stuff from and of course you shouldn't let 
every database user to use it, just those that are authorized to  do the 
job.

mysql -hhost -uuser -p database -e  "CALL sp_you_created;"

-- 
  //Aho