SQLite3 Query storing IP address syntax error

Question

I'm trying to run a sqlite3 query against a small DB to update a table. While the script is more complicated, for testing I set the following variables:

DN=123
UP=123
downlocalip=10.1.2.3
downremoteip=123
uplocalip=123
upremoteip=123

I then run the following command to update the table.

sqlite3 /var/www/server/newserverstats.db "UPDATE stats SET downspeed='''$DN''', upspeed='''$UP''', downlocalip='''$downlocalip''', downremoteip='''$downremoteip''', uplocalip='''$uplocalip''', upremoteip='''$upremoteip''' WHERE primkey=1"

This throws a syntax error:

Error: near ".2": syntax error

If I set the downlocalip as 10.1 only, it works fine, so it is not liking the additional decimals.

On the table itself I have the type set as text so I didn't think it would matter?

Pragma output of table:

0|primkey|integer|0||1
1|downspeed|integer|1||0
2|upspeed|integer|1||0
3|downlocalip|text|1||0
4|downremoteip|text|1||0
5|uplocalip|text|1||0
6|upremoteip|text|1||0

I have tried all sorts of quotation mark setups but can't see what I'm doing wrong.

Any ideas?

EDIT:

The full commands I have tried as per comments below are:

/usr/bin/ssh [email protected] 'sqlite3 /var/www/server/newserverstats.db "UPDATE stats SET downspeed=$DN, upspeed=$UP, downlocalip="$downlocalip", downremoteip="$downremoteip", uplocalip="$uplocalip", upremoteip="$upremoteip" WHERE primkey=1"'

or

/usr/bin/ssh [email protected] 'sqlite3 /var/www/server/newserverstats.db "UPDATE stats SET downspeed=$DN, upspeed=$UP, downlocalip='$downlocalip', downremoteip='$downremoteip', uplocalip='$uplocalip', upremoteip='$upremoteip' WHERE primkey=1;"'

both give me the following error:

Error: near ",": syntax error

New command, which is closer:

ssh [email protected] sqlite3 /var/www/server/newserverstats.db <<END_SQL
    UPDATE stats
    SET downspeed=$DN,
        upspeed=$UP,
        downlocalip="$downlocalip",
        downremoteip="$downremoteip",
        uplocalip="$uplocalip",
        upremoteip="$upremoteip"
    WHERE primkey=1
END_SQL

Collapsing back to a single line in a test script:

#!/bin/bash -x
DN=123
UP=123
downlocalip=10.1.2.3
downremoteip=123
uplocalip=123
upremoteip=123
sql="UPDATE stats SET downspeed=$DN, upspeed=$UP, downlocalip="$downlocalip", downremoteip="$downremoteip", uplocalip="$uplocalip", upremoteip="$upremoteip" WHERE primkey=1"
echo $sql
ssh [email protected] sqlite3 /var/www/server/newserverstats.db "$sql"

This gives me the response of:

UPDATE stats SET downspeed=123, upspeed=123, downlocalip=10.1.2.3, downremoteip=123, uplocalip=123, upremoteip=123 WHERE primkey=1
sqlite3: Error: too many options: "stats"
Use -help for a list of options.

Answer 1

The issue is the quoting of the strings in the text fields.

Use a here-document (which enables you to write a nicer looking statement):

sqlite3 database <<END_SQL
    UPDATE stats
    SET downspeed=$DN,
        upspeed=$UP,
        downlocalip="$downlocalip",
        downremoteip="$downremoteip",
        uplocalip="$uplocalip",
        upremoteip="$upremoteip"
    WHERE primkey=1
END_SQL

This is assuming that you have full control over the values in the variables so that you know that they are properly sanitized and won't introduce any SQL injection vulnerability.

---

From comments:

Doing this over SSH:

ssh user@server sqlite3 database <<END_SQL
    UPDATE stats
    SET downspeed=$DN,
        upspeed=$UP,
        downlocalip="$downlocalip",
        downremoteip="$downremoteip",
        uplocalip="$uplocalip",
        upremoteip="$upremoteip"
    WHERE primkey=1
END_SQL

Answer 2

The main problem is that you're using three single-quotes before and after each variable instead of just one. This isn't necessary - in fact, it won't work.

You're getting the error on the IP address $downlocalip because it's effectively unquoted, so sqlite3 is trying to interpret it as a floating point number rather than a text string...and floating point numbers do not have two decimal points in them.

Another, minor, problem is that you're putting single-quotes around values for the integer fields (downspeed and upspeed).

Try this instead:

sqlite3 /var/www/server/newserverstats.db "UPDATE stats SET downspeed=$DN,
   upspeed=$UP, downlocalip='$downlocalip', downremoteip='$downremoteip',
   uplocalip='$uplocalip', upremoteip='$upremoteip' WHERE primkey=1;"

The single-quotes in that string are inside the double-quotes, so do not prevent the variables from being expanded by the shell. They have no special meaning inside double-quotes, they're just part of the text. The variables do have special meaning inside double-quotes so do get expanded as you'd expect.

Or use a heredoc as in Kusalananda's answer - it's more readable.

BTW, if you're doing a LOT of sqlite operations, you'd be much better off writing your script in a language that has an sqlite library module that supports placeholder values. e.g. both perl and python have excellent libraries for working with sqlite3 and other SQL databases. You can usually write code using these libraries so that they require few, if any, changes to work with other databases such as postgresql or mysql.