0

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 pi@10.0.0.16 '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 pi@10.0.0.16 '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 pi@10.0.0.16 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 pi@10.0.0.16 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.
  • try it without the single-quotes around the entire sqlite3 ... command. they are conflicting with the single-quotes inside the double-quotes. And there's nothing about the command that needs single quotes - ssh passes all its remaining arguments (after user@host and option processing, if any) to the remote shell anyway. – cas Mar 7 '18 at 11:04
  • alternatively, you could replace every single-quote (the ones inside the double-quotes) with '\'' (end-quote, escaped-quote, start-quote) but that would be extremely ugly and difficult to read. – cas Mar 7 '18 at 11:08
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
  • Comments are not for extended discussion; this conversation has been moved to chat. – terdon Mar 10 '18 at 1:02
0

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.

  • thanks for the quick reply, I get the same error using this version as I do with Kusalanada's - I'll update my question with the full command in case it is something else in the command – Matthew Hodder Mar 7 '18 at 10:59
  • OK, it seems you're having a problem with nested quotes. They can be quite difficult to deal with, not impossible but very easy to get lost in the multiple levels of quoting involved. – cas Mar 7 '18 at 11:01
  • thanks - I get the error 'too many options' using this one? – Matthew Hodder Mar 7 '18 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.