Inserting Linux command output into MySQL within Bash script

Question

I have a script that scans an IP address for an open FTP port and then inputs that IP address into a MySQL database. However, upon running the script, I receive the following message:

"ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '192.168.1.104 INSERT INTO users (ip) VALUES (192.168.1.104)' at line 1"

I have set up the database and named it 'nmapscans' with a table named 'users' and field name 'ip' that is of the data type 'int(20)'

Here is my incomplete script:

#!/bin/bash

ipadd=`ifconfig wlan0 | awk '/inet / {print $2}' | cut -d: -f2`
nmap -sV $ipadd -p 21 -oG ftp1 ; cat ftp1 | grep closed > ftp2
cut -d: -f2 ftp2 | cut -d"(" -f1 > ftp3
#cat ftp3


function checkDatabase {
    RESULT=`mysql -u root -pQwerty17 --skip-column-names -e "SHOW DATABASES LIKE             'nmapscans'"`
    if [ "$RESULT" == "nmapscans" ]; then
        echo "Database exists"
    else
        echo "Database does not exist"
    fi
}

checkDatabase && echo $?

echo "INSERT INTO users (ip) VALUES ("100");" | mysql -uroot -pQwerty17 nmapscans;

function input_data { 
    #if [[ checkDatabase ]]
    inputfile="ftp3"
    cat $inputfile | while read ip; do
        echo "$ip"
        echo "INSERT INTO users (ip) VALUES ("$ip");"
    done | mysql -uroot -pQwerty17 nmapscans;
    #fi 
}

input_data && echo $?

if [[ input_data == 0 ]]
    then 
        echo "it worked" && rm ftp1 ftp2 ftp3
    else    
        echo "it failed"
fi
exit

You're echoing the IP in between your mysql insert statments. Mysql doesn't know what to do with that. Just remove the echo "$ip" line. — Florin Stingaciu
– Florin Stingaciu, Commented Jul 24, 2014 at 17:13
@FlorinStingaciu Great! that worked. However, something that is still confusing me is when I run the script, the status code for the input_data function is 0 but the output of the if statement at the end of the script says 'it failed' — UmairK
– UmairK, Commented Jul 24, 2014 at 17:42

Florin Stingaciu · Accepted Answer · 2014-07-24 17:47:52Z

3

You're echoing the IP in between your mysql insert statements. Mysql doesn't know what to do with that. Just remove the echo "$ip" line.

Change:

function input_data { 
    #if [[ checkDatabase ]]
    inputfile="ftp3"
    cat $inputfile | while read ip; do
        echo "$ip"
        echo "INSERT INTO users (ip) VALUES ("$ip");"
    done | mysql -uroot -pQwerty17 nmapscans;
    #fi 
}

to:

function input_data { 
    #if [[ checkDatabase ]]
    inputfile="ftp3"
    cat $inputfile | while read ip; do
        echo "INSERT INTO users (ip) VALUES ("$ip");"
    done | mysql -uroot -pQwerty17 nmapscans;
    #fi 
}

Also regarding your question about the return statement. Change:

input_data && echo $?

if [[ input_data == 0 ]]
    then 
        echo "it worked" && rm ftp1 ftp2 ftp3
    else    
        echo "it failed"
fi
exit

to:

input_data 

if [[ $? == 0 ]]
    then 
        echo "it worked" && rm ftp1 ftp2 ftp3
    else    
        echo "it failed"
fi
exit

answered Jul 24, 2014 at 17:47

Florin Stingaciu

8,3532 gold badges26 silver badges45 bronze badges

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

2 Comments

UmairK Over a year ago

Amazingly it worked. Thanks for your support and prompt reply.

tripleee Over a year ago

The proper, idiomatic way to test for success is simply if input_data; then ... or even just input_data && rm ftp[123] || echo "it failed" >&2

Sylvain Leroux · Accepted Answer · 2014-07-24 17:19:57Z

2

You only miss proper quoting:

    echo "INSERT INTO users (ip) VALUES ('"$ip"');"
    #                                    ^     ^

Thus, the IP address is quoted as a string, producing that (now valid) SQL statement:

INSERT INTO users (ip) VALUES ('192.168.1.104');

edited Jul 24, 2014 at 17:19

answered Jul 24, 2014 at 17:14

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

3 Comments

UmairK Over a year ago

Unfortunately I'm still getting the same error message even after having made the suggested change. I also changed the datatype of the column in MySQL to char but still no luck.

Florin Stingaciu Over a year ago

@UmairK See my comment above.

Sylvain Leroux Over a year ago

@FlorinStingaciu Thank you Florin for pointing out those various issues. BTW, how 192.168.1.104 could be properly understood by MySQL as an int(20)? Obviously there is still one thing that I missed here !

konsolebox · Accepted Answer · 2014-07-25 04:15:16Z

There are at least two ways to show a message to user while still being able to redirect others to intended output. And cat is also not necessary here. Better not trouble it.

Sending the message to stderr (default &2) instead:

while read ip; do
    echo "$ip" >&2
    echo "INSERT INTO users (ip) VALUES ($ip);"
done < "$inputfile" | mysql -uroot -pQwerty17 nmapscans;

Using other file descriptors with process substitution:

while read ip; do
    echo "$ip"
    echo "INSERT INTO users (ip) VALUES ($ip);" >&4
done < "$inputfile" 4> >(exec mysql -uroot -pQwerty17 nmapscans)

It's also a good idea not to place your variables in the open to prevent word splitting. It may not apply now but it's still a good practice. Notice how I fixed it:

echo "INSERT INTO users (ip) VALUES ($ip);"

At your option you may also add quotes:

echo "INSERT INTO users (ip) VALUES ('$ip');"

Collectives™ on Stack Overflow

Inserting Linux command output into MySQL within Bash script

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related