CODEWITHSUNDEEP
mysqllinuxbash
UmairK
UmairK

Reputation: 29

Inserting Linux command output into MySQL within Bash script

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

Upvotes: 2

Views: 3890

Answers (3)

konsolebox
konsolebox

Reputation: 75588

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');"

Upvotes: 1

Florin Stingaciu
Florin Stingaciu

Reputation: 8295

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

Upvotes: 3

Sylvain Leroux
Sylvain Leroux

Reputation: 52060

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');

Upvotes: 2

Related Questions