4

I wrote a Bash script to insert values to an SQLite database. The command is as follows:

sqlite3 ${db_name} "insert into ${table_name} (${column1},${column2}) values ('$f1','$f2');"

This command works fine until the f1 variable contains a single quote:

# E.g., f1="I'm just kidding"
# The command reported an error:
Error: near "m": syntax error

How can we escape the single quote inside the variable?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Dien Nguyen
  • 2,019
  • 6
  • 30
  • 37
  • 1
    you are aware of the possibility of [SQL injection](http://en.wikipedia.org/wiki/SQL_injection) in this script.. yes ? – Oren Jan 15 '13 at 14:11
  • My recommendation? Use a programming language. – Ignacio Vazquez-Abrams Jan 15 '13 at 14:11
  • Hi @Oren, actually I am not good at SQL, I just made the script to automatically add entry by entry manually to the db – Dien Nguyen Jan 15 '13 at 14:19
  • if this is for personal use only I guess its fine. but if there's even a chance of this script being used by other people, the way it is written now leaves your database vulnerable to attacks. the answer to this is input validation, which would be hell to hard to implement in a script. @IgnacioVazquez-Abrams 's recommendation is a good one in that case. – Oren Jan 15 '13 at 14:27
  • The canonical is probably *[How to escape single quotes within single quoted strings](https://stackoverflow.com/questions/1250079/)* (2009. 26 answers. 1380 votes). See [this answer](https://stackoverflow.com/questions/1250079/how-to-escape-single-quotes-within-single-quoted-strings/16605140#16605140) for a relatively simple syntax. – Peter Mortensen Aug 16 '23 at 18:36

3 Answers3

4

From Bash, you can use ${varname//x/y} to replace all instances of x with y in the varname variable.

sqlite3 ${db_name} "insert into ${table_name} (${column1},${column2}) values ('${f1//\'/\'}','${f2//\'/\'}');"

will replace any ' with ' though @ignacioVazquez-Abrams has the best answer as the PHP, Perl, and Python implementations all have modules to help sanitise input.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
peteches
  • 3,447
  • 1
  • 13
  • 15
  • Hi @peteches, thank you for your answer. It returns "Bad substitution" – Dien Nguyen Jan 15 '13 at 14:26
  • 1
    yeah sorry rushed it a bit and put the escaping backslash in the wrong place. Edited now so should work. if not your bash version may not support the replacement can't remember what version it came in on – peteches Jan 15 '13 at 14:33
4

To escape a single quote for SQL, you double it (https://www.sqlite.org/faq.html#q14):

$ f1="I'm just kidding"
$ echo "${f1//\'/''}"
I''m just kidding
$ f2="no single quotes"
$ echo "${f2//\'/''}"
no single quotes

So

sqlite3 ${db_name} "insert into ${table_name} (${column1},${column2}) values ('${f1//\'/''}','${f2//\'/''}');"
glenn jackman
  • 238,783
  • 38
  • 220
  • 352
0

You could use \

f1="I\'m just kidding"
GioLaq
  • 2,489
  • 21
  • 26
  • Hi JoaoBiriba, the `f1` variable is read from file, so it can be arbitrary string. Should we pre-process the file before execute the script? – Dien Nguyen Jan 15 '13 at 14:15
  • so you can pre-process your file using sed for example: sed 's///' – GioLaq Jan 15 '13 at 14:19
  • I ***don't*** think that works, at least not from the command line. `echo $f1` returns `I\'m just kidding` (including the backslash). Tested with Bash 4.4.20 on [Ubuntu](https://en.wikipedia.org/wiki/Ubuntu_%28operating_system%29). – Peter Mortensen Aug 21 '23 at 16:26
  • [cont'](https://stackoverflow.com/questions/50520041/how-to-escape-a-single-quote-on-linux/50520709#50520709) - *"The* ***only*** *thing that has a special meaning after a single-quote is another single-quote"* – Peter Mortensen Aug 21 '23 at 21:35