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...

Regards.
^Bart

On 31/10/2023 15: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...
>
> Regards.
> ^Bart

I'd almost certainly write that in C or PHP. The APIS are very simple
for something like that.

--
If I had all the money I've spent on drink...
...I'd spend it on drink.

Sir Henry (at Rawlinson's End)

Am 31.10.2023 um 16:42:12 Uhr schrieb ^Bart:

> 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! :)

At first glace, that looks good, give it a try on a test database.

On 10/31/2023 11:42 AM, ^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...
>
> Regards.
> ^Bart

You don't need anything but SQL and the CLI.

Just create a text file with the SQL commands necessary to use the
database, truncate the table and import from the file. Call it
reload.sql, for instance.

Then use the CLI source command to run that file, i.e.

mysql -u user -ppassword
source reload.sql

Or you could do it with

mysql -u user -ppassword < reload.sql

It's not a good idea to have the password in a file.

--
==================
Remove the "x"'s from my email address
Jerry Stuckle
stucklex.jerryx@gmail.com
==================

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

> I'd almost certainly write that in C or PHP.  The APIS are very simple
> for something like that.

Thanks for your reply, I used PHP years and years ago and I don't
remember it very well but really thanks to share this solution! :)

^Bart

>> 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! :)
>
> At first glace, that looks good, give it a try on a test database.

Thanks for your reply, sure I'll do it in a test db! :)

^Bart

> You don't need anything but SQL and the CLI.

Ok!

> Just create a text file with the SQL commands necessary to use the
> database, truncate the table and import from the file.  Call it
> reload.sql, for instance.

Ok.

> Then use the CLI source command to run that file, i.e.
>
> mysql -u user -ppassword
> source reload.sql

So... I should have a file where I'll store the login data and I could
name it like "login.sql" and inside of it I should insert the "reload.sql".

At the end I should add the link to "login.sql" in crontab because I
should run it every 5 minutes.

> Or you could do it with
>
> mysql -u user -ppassword < reload.sql

Ok.

> It's not a good idea to have the password in a file.

I know but this is a test machine inside of my lan, it's not in DMZ and
just me or one colleague can login to it but you got the point about
security risks and in your opinion could I solve this "issue" with the
idea showed in the link which I posted in my first message or do you
have another idea?

Regards.
^Bart

> 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.

I didn't think about this issue... :\

> 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.

Yes, this is what I need a cron job to automate the process.

> 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/

[CUT]

I didn't know it and this is a good idea to start to study something new
to improve "my skills" (and obviously to improve the security field!);
I'm working on a test machine in our lan (not in DMZ) so just me and one
colleague can login there, now I need to solve this "problem" because
another colleague is waiting me with Knime to do queries from MariaDB
and I'll use the dirty way! :)

Really thanks also to you for this great reply! :)

Have a nice day!
^Bart

> Thanks for your reply, I used PHP years and years ago and I don't
> remember it very well but really thanks to share this solution! :)

I did in my crontab this:

*/5 * * * * /usr/bin/mysql -u my_username -h localhost -p my_password <
/home/itoffice/query.sql

It doesn't work... :/

If I run query.sql after I did a manual login in MariaDB it works.

I think I must use php like what this site said:
https://stackoverflow.com/questions/9948220/how-can-i-set-a-crontab-to-execute-a-mysql-query-and-log-the-output

I think also I should save php file not in my home folder like
/home/itoffice but in /var/www/html right?

^Bart

On 02/11/2023 09.50, ^Bart wrote:
>> You don't need anything but SQL and the CLI.
>
> Ok!
>
>> Just create a text file with the SQL commands necessary to use the
>> database, truncate the table and import from the file.  Call it
>> reload.sql, for instance.
>
> Ok.
>
>> Then use the CLI source command to run that file, i.e.
>>
>> mysql -u user -ppassword < reload.sql
>
> So... I should have a file where I'll store the login data and I could
> name it like "login.sql" and inside of it I should insert the "reload.sql".

No, the sql file will only include the raw SQL queries in the same way
as you would have when you type in the mysql after you logged in.

Credentials can be stored in a configuration file, here is an example
for MySQL, but works exactly the same way for MariaDB:

https://www.serverlab.ca/tutorials/linux/database-servers/how-to-create-a-credential-file-for-mysql/

In you case I would maybe create a new user, create a profile (I hope
this part is in MariaDB). See to that the default login shell is
/sbin/nologin and then you see to that the cronjob is run as this user
(in this example: dbcronuser).

* 5 * * * dbcronuser (mysql --login-path=backups <
/home/dbcronuser/reload.sql)

--
//Aho

On 02/11/2023 11.46, ^Bart wrote:
>> Thanks for your reply, I used PHP years and years ago and I don't
>> remember it very well but really thanks to share this solution! :)
>
> I did in my crontab this:
>
> */5 * * * * /usr/bin/mysql -u my_username -h localhost -p my_password <
> /home/itoffice/query.sql

this in /etc/crontab or did you use it as your personal crontab with
"crontab -e"?

if the later, then your user need to have the right to read the file and
of course have the right to access the content in the itoffice directory.

if /etc/crontab then add who is executing the command and it can be good
to put parentheses around command too

*/5 * * * * (root) (/usr/bin/mysql -umy_username -pmy_password
databasename <
/home/itoffice/query.sql)

> I think I must use php like what this site said:
> https://stackoverflow.com/questions/9948220/how-can-i-set-a-crontab-to-execute-a-mysql-query-and-log-the-output

No you don't, it's just what he did.

> I think also I should save php file not in my home folder like
> /home/itoffice but in /var/www/html right?

That is much about how you use the php script, if you use it like a bash
script then no you don't put it there. If it's part of your web site,
then yes.

Php can be used for websites, scripting and even create gui
applications, it may not be the best option in all situations.

--
//Aho

> */5 * * * * (root) (/usr/bin/mysql -umy_username -pmy_password
> databasename <
>  /home/itoffice/query.sql)

*/5 * * * * (itoffice) (/usr/bin/mysql -u username -p password db_name <
/home/it_office/query.sql)

It doesn't work :\ but it doesn't work also if I insert it in a php
script; here below is the working *.sql file used from MariaDB CLI:

USE db_name;
TRUNCATE items_temp;

LOAD DATA LOCAL INFILE '/home/itoffice/data_bridge/items.txt'
INTO TABLE items_temp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id_tc_anartic, cod_art, desc_art, ump, umi, coef_ump_umi, gest_maga,
cod_iva, cod_cat_cont, desc_cat_cont, cod_class_pat, art_stato,
disabilitato, ean, peso_netto, peso_lordo, um_peso, lunghezza,
larghezza, altezza, diametro , cod_cat01 , desc_cat01 ,
cod_cat02, desc_cat02, cod_cat03, desc_cat03, cod_cat04, desc_cat04,
cod_cat05, desc_cat05, cod_cat06, desc_cat06, cod_cat07,
desc_cat07, cod_cat08, desc_cat08, cod_cat09, desc_cat09, cod_cat10,
desc_cat10, cod_cat11, desc_cat11, cod_cat12, desc_cat12,
cod_cat13, desc_cat13, cod_cat14, desc_cat14, cod_cat15, desc_cat15,
cod_cat16, desc_cat16, cod_cat17, desc_cat17, cod_cat18,
desc_cat18, cod_cat19, desc_cat19, cod_cat20, desc_cat20, dt_creazione,
dt_last_upd);

I tried to insert it in a php file but in this case nothing happen :\

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Parametri da inserire per collegarsi al DB
$servername = "localhost";
$username = "username";
$password = "password";
$database = "database_name";
$options = [MYSQLI_OPT_LOCAL_INFILE => true];
$conn = mysqli_connect($servername, $username, $password, $database);
$conn->options(MYSQLI_OPT_LOCAL_INFILE, true);

if (!$conn) {
die("Connessione al database fallita: " . mysqli_connect_error());
}

// Query SQL
$query = "USE database_name;
TRUNCATE items_temp;

LOAD DATA LOCAL INFILE '/home/itoffice/data_bridge/items.txt'
INTO TABLE items_temp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id_tc_anartic, cod_art, desc_art, ump, umi, coef_ump_umi, gest_maga,
cod_iva, cod_cat_cont, desc_cat_cont, cod_class_pat, art_stato,
disabilitato, ean, peso_netto, peso_lordo, um_peso, lunghezza,
larghezza, altezza, diametro, cod_cat01, desc_cat01, cod_cat02,
desc_cat02, cod_cat03, desc_cat03, cod_cat04, desc_cat04, cod_cat05,
desc_cat05, cod_cat06, desc_cat06, cod_cat07, desc_cat07, cod_cat08,
desc_cat08, cod_cat09, desc_cat09, cod_cat10, desc_cat10, cod_cat11,
desc_cat11, cod_cat12, desc_cat12, cod_cat13, desc_cat13, cod_cat14,
desc_cat14, cod_cat15, desc_cat15, cod_cat16, desc_cat16, cod_cat17,
desc_cat17, cod_cat18, desc_cat18, cod_cat19, desc_cat19, cod_cat20,
desc_cat20, dt_creazione, dt_last_upd)";

if (mysqli_multi_query($conn, $query)) {
echo "Query eseguita con successo.";
} else {
echo "Errore nell'esecuzione della query: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

I opened the php.ini file and I have:

; Allow accessing, from PHP's perspective, local files with LOAD DATA
statements
; https://php.net/mysqli.allow_local_infile
mysqli.allow_local_infile = On

Regards.
^Bart

On 02/11/2023 15.07, ^Bart wrote:
>> */5 * * * * (root) (/usr/bin/mysql -umy_username -pmy_password
>> databasename <
>>   /home/itoffice/query.sql)
>
> */5 * * * * (itoffice) (/usr/bin/mysql -u username -p password db_name <
> /home/it_office/query.sql)
>
> It doesn't work :\ but it doesn't work also if I insert it in a php
> script; here below is the working *.sql file used from MariaDB CLI:

What error mail is sent from crontab? should be sent to the local
machines root user unless you have set MAILTO to another email address.

in worst case use less and read the /var/spool/mail/root should be in
the end of the file.
sure you can use the alpine or mutt to read the mail too.

By the way, do you have characters like '#;" in your password? Those
tend to cause issues when using this way. I would recommend the usage of
profile.

--
//Aho

On 11/2/2023 6:46 AM, ^Bart wrote:
>> Thanks for your reply, I used PHP years and years ago and I don't
>> remember it very well but really thanks to share this solution! :)
>
> I did in my crontab this:
>
> */5 * * * * /usr/bin/mysql -u my_username -h localhost -p my_password <
> /home/itoffice/query.sql
>
> It doesn't work... :/
>
> If I run query.sql after I did a manual login in MariaDB it works.
>
> I think I must use php like what this site said:
> https://stackoverflow.com/questions/9948220/how-can-i-set-a-crontab-to-execute-a-mysql-query-and-log-the-output
>
> I think also I should save php file not in my home folder like
> /home/itoffice but in /var/www/html right?
>
> ^Bart
>

No space between your -p and your password. This is different from
other parameters.

--
==================
Remove the "x"'s from my email address
Jerry Stuckle
stucklex.jerryx@gmail.com
==================

> No space between your -p and your password.  This is different from
> other parameters.

You got the point, it works without space from -p and password but...

It works from command line:
$ mysql -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql

It doesn't work from crontab -e also from root:
*/5 * * * * -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql

I don't know why... :\

^Bart

On 03/11/2023 14:42, ^Bart wrote:
>> No space between your -p and your password.  This is different from
>> other parameters.
>
> You got the point, it works without space from -p and password but...
>
> It works from command line:
> $ mysql -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql
>
> It doesn't work from crontab -e also from root:
> */5 * * * * -u MY_USER -pMY_PASSWORD < /home/itoffice/query.sql
>
> I don't know why... :\
>
perhaps adding 'mysql' would help?

> ^Bart
>

--
"Nature does not give up the winter because people dislike the cold."

― Confucius

> perhaps adding 'mysql' would help?
>

I did it but I didn't past it on my reply! :)

I also inserted the full path of Mysql but... nothing appened...

^Bart

On 04/11/2023 09.42, ^Bart wrote:
>> perhaps adding 'mysql' would help?
>>
>
> I did it but I didn't past it on my reply! :)
>
> I also inserted the full path of Mysql but... nothing appened...

Did you look at the mail from crontab? by default it goes to the
localhost root user (you can change that with setting the MAILTO
variable), if something wen t wrong while running the mysql command it
will be in that mail (if no output, no mail).

You could check your /var/log/mysql/mysql.{err,log} too

I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.

--
//Aho

On 11/4/2023 5:09 AM, J.O. Aho wrote:
> On 04/11/2023 09.42, ^Bart wrote:
>>> perhaps adding 'mysql' would help?
>>>
>>
>> I did it but I didn't past it on my reply! :)
>>
>> I also inserted the full path of Mysql but... nothing appened...
>
> Did you look at the mail from crontab? by default it goes to the
> localhost root user (you can change that with setting the MAILTO
> variable), if something wen t wrong while running the mysql command it
> will be in that mail (if no output, no mail).
>
> You could check your /var/log/mysql/mysql.{err,log} too
>
> I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.
>

The space after the -u is fine. It's only the password that can't have
the space.

--
==================
Remove the "x"'s from my email address
Jerry Stuckle
stucklex.jerryx@gmail.com
==================

> Did you look at the mail from crontab? by default it goes to the
> localhost root user (you can change that with setting the MAILTO
> variable), if something wen t wrong while running the mysql command it
> will be in that mail (if no output, no mail).
>
> You could check your /var/log/mysql/mysql.{err,log} too
>
> I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.

I found this solutions:

*/5 * * * * /home/itoffice/job01.sh

This is job01.sh

#!/bin/bash
#sostituisce tutte le virgole presenti nel file con dei punti
sed -i -e 's/,/./gip' /home/itoffice/data_bridge/articoli.txt

/usr/bin/mysql -umy_username -pmy_password < /home/itoffice/articoli.sql

#copia del file importato nella directory di backup
mv -v /home/itoffice/data_bridge/articoli.txt
"/home/itoffice/data_bridge_bck/articoli_$(date +"%d-%m-%Y_%H-%M".txt)"

I found these information to fix php load data:

https://itecnote.com/tecnote/php-load-data-local-infile-forbidden-after-php-mariadb-update/
https://itecnote.com/tecnote/php-load-data-local-infile-forbidden-after-php-mariadb-update/

But now I need to do other things in my company so... I know it's a
dirty way to use a *.sh file but I dont't have free time to fix it maybe
next weeks... ;)

Have a nice day!
^Bart

On 05/11/2023 11:23, ^Bart wrote:
>> Did you look at the mail from crontab? by default it goes to the
>> localhost root user (you can change that with setting the MAILTO
>> variable), if something wen t wrong while running the mysql command it
>> will be in that mail (if no output, no mail).
>>
>> You could check your /var/log/mysql/mysql.{err,log} too
>>
>> I hope the "-u MY_USER" was a typo, otherwise loose the space after '-u'.
>
> I found this solutions:
>
> */5 * * * * /home/itoffice/job01.sh
>
> This is job01.sh
>
> #!/bin/bash
> #sostituisce tutte le virgole presenti nel file con dei punti
> sed -i -e 's/,/./gip' /home/itoffice/data_bridge/articoli.txt

Yes, the root will not have the same LANG settings as your user and
those you need to change the comma to full stop for decimal values.
You could have added the same LANG/LC to the cron and this hadn't been a
issue.

> But now I need to do other things in my company so... I know it's a
> dirty way to use a *.sh file but I dont't have free time to fix it maybe
> next weeks... ;)

Tend to become one of those things that will be never fixed and next
time another ad-hoc solution is used to fix next issue.

--
//Aho