CODEWITHSUNDEEP
mysqlsqllinuxbash
Kevin Horejs
Kevin Horejs

Reputation: 13

How to avoid special/escape characters in MySQL/BaSH

I have been working to insert strings from text files into a MySQL database. However in these strings there could be random escape characters that will effect either BaSH or MySQL. Originally I was trying to pass the entire text file of characters to MySQL and deal with it in there. However now I think it may be better to cut up the data and deal with it mostly in BaSH.

I am running everything from script files and am not the best with BaSH, I actually work mostly with MySQL or other languages like C# or Java.

I know that you can embed other languages into BaSH scripts however in the end I will still need to pass strings to MySQL to insert and I need to preserve the original values of the text files.

So, is there a way to get these strings to MySQL with the languages to just ignore the characters in the string so it will preserve it and not cause issues?

#!/bin/bash
cd /srv/mysql_PROJ/DB_scripts
echo ""
for dir in `cat ../pathlist.txt`; do
    for file in `/bin/ls -1 ${dir}/*.txt`; do
echo "Processing: dir(${dir}) / file(${file}); `cat ${file} | wc`"
    mysql -u rpm -p'password' --show-warnings --max_allowed_packet=16M call rpmdb.DataButcher( _utf8'`cat ${file}`');

    echo "Data inserted for:  `cat ${dir}`"
    echo ""
done
done
echo "Data parse and insert: Done."

This is the code I had before then I had a much more complex MySQL procedure that dealt with it. From what I could tell it would hit a character and pass only portions of the text to MySQL which then caused problems in MySQL, and the following lines in the text file tried to run as BaSH and obviously they weren't BaSH commands. Hopefully this will help, this is a older project that I keep coming back to and last I looked at it I got hitched up. With a new and fresh mind I am trying to approach it differently.

NOTE: the procedure in MySQL is called "DataButcher"

Upvotes: 0

Views: 933

Answers (1)

abasterfield
abasterfield

Reputation: 2292

  1. Preferably don't use bash and the command line client instead use a scripting language like perl with mysql libraries. There you can use placeholders ? instead of string values and pass them arguments separately outside of the SQL.

  2. At a pinch you can use mysql UNHEX function

    INSERT INTO foo (bar) VALUES (UNHEX("536166652066726f6d2053514c20696e6a656374696f6e2027"));

Upvotes: 1

Related Questions