ユーザがコメントに「'(シングルクオート)」を入力してきてもエラーにならないようにしたい

Question

Python 3.6.5
SQLite 3.28.0
select * from twitter_users;でシングルクオートが消えているのを確認しました。

以下のスクリプトを書きました

import sqlite3


class Database:
    def __init__(self):
        self.dbpath = 'db.sqlite3'
        self.conn = sqlite3.connect(self.dbpath)
        self.c = self.conn.cursor()


db = Database()


def update_comment(name, comment='null'):
    db.c.execute("update twitter_users set comment = '" + comment + 'where name = "' + name + '"')
    db.conn.commit()


update_comment('Jikkenndesu', "How's day?")

エラー文：
Traceback (most recent call last):
File "test.py", line 19, in <module>
update_comment('Jikkenndesu', "How's day?")
File "test.py", line 15, in update_comment
db.c.execute("update twitter_users set comment = '" + comment + 'where name = "' + name + '"')
sqlite3.OperationalError: near "s": syntax error

こちらでもダメでした。

import sqlite3


class Database:
    def __init__(self):
        self.dbpath = 'db.sqlite3'
        self.conn = sqlite3.connect(self.dbpath)
        self.c = self.conn.cursor()


db = Database()


def update_comment(name, comment='null'):
    db.c.execute('update twitter_users set comment = %s where name = %s' % (name, comment))
    db.conn.commit()


update_comment('Jikkenndesu', "How's day?")

エラー：
Traceback (most recent call last):
File "test.py", line 19, in <module>
update_comment('Jikkenndesu', "How's day?")
File "test.py", line 15, in update_comment
db.c.execute('update twitter_users set comment = %s where name = %s' % (name, comment))
sqlite3.OperationalError: unrecognized token: "'s day?"

ユーザがコメントに「'(シングルクオート)」を入力してきてもエラーにならないようにしたいのです。

アドバイスとご教授のほどよろしくお願いします。

Comments

[Zuishin]

https://teratail.com/questions/219697
回答しています。解決したなら解決済みにしてください。していないのならなぜしていないのかを書いてください。

[kalon]

不愉快な思いをさせてしまって申し訳ございません。
そちらの質問にコメントさせていただきました。

Answer 1

公式ドキュメントを読めば十分です。

https://docs.python.org/ja/3.7/library/sqlite3.html

たいてい、SQL 操作では Python 変数の値を使う必要があります。この時、クエリーを Python の文字列操作を使って構築することは安全とは言えないので、すべきではありません。そのようなことをするとプログラムが SQL インジェクション攻撃に対し脆弱になります (https://xkcd.com/327/ ではどうなってしまうかをユーモラスに描いています)。

代わりに、DB-API のパラメータ割り当てを使います。 ? を変数の値を使いたいところに埋めておきます。その上で、値のタプルをカーソルの execute() メソッドの第2引数として引き渡します。(他のデータベースモジュールでは変数の場所を示すのに %s や :1 などの異なった表記を用いることがあります。) 例を示します:

---

コメントへ

import sqlite3

conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')

c.execute("INSERT INTO stocks VALUES ('2006-01-05', ? ,'RHAT',100,35.14)", ("ho'ge",))

# Save (commit) the changes
conn.commit()

for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

結果

('2006-01-05', "ho'ge", 'RHAT', 100.0, 35.14)

パッとやる限りは再現しないですね……。
DBを中身をどうやって確認したかと、Pythonのバージョンを明記してもらえるといいかと思いました。

---

追記

db.c.execute('update twitter_users set comment = ? where name = ?', (name, comment))

だとnameとcommentが逆ですから、where句でヒットしないので0件のupdateで終了します。updateする前の情報を見て勘違いしているだけではないでしょうか。

db.c.execute('update twitter_users set comment = ? where name = ?', (comment, name))

か

db.c.execute('update twitter_users set comment = :comment where name = :name', {'name': name, 'comment': comment})

にしましょう。

Comments

[kalon]

```Python
def update_comment(name, comment='null'):
db.c.execute('update twitter_users set comment = ? where name = ?', name, comment)
db.conn.commit()

update_comment('Jikkenndesu', "How's day?")
```
エラー文：
Traceback (most recent call last):
File "test.py", line 21, in <module>
update_comment('Jikkenndesu', "How's day?")
File "test.py", line 17, in update_comment
db.c.execute('update twitter_users set comment = ? where name = ?', name, comment)
TypeError: function takes at most 2 arguments (3 given)
変数を２つだけ割り当てたのに、３つ与えられたと言われます・・・。何故でしょうか？

[kalon]

db.c.execute('update twitter_users set comment = ? where name = ?', (name, comment))

でできました！ありがとうございます。

[kalon]

でも、ＤＢ内で
「How's day?」　が「Hows day?」になってしまいます。どうしたら「'」を残せるでしょうか？

[quickquip]

追記しました。情報を質問に追記して(これ大事)、質問を未解決に戻してもいいと思います。

[kalon]

Python 3.6.5
SQLite 3.28.0
select * from twitter_users;でシングルクオートが消えているのを確認しました。

[quickquip]

情報(状況)は質問に追記しましょう。
今のコードがどうなっていて、何をして、何を確認したか、です。

[quickquip]

あと回答済みです。追記もしておきますが。

Answer 2

こういうコードは、エラーになればまだしも、勝手に自由にSQLを実行されてしまう可能性があります。

インターネットからアクセスできるサイトを作る場合は、攻撃者からサイトを守ることについての知識を付けましょう。
少なくとも下記レベルは必要です。

脆弱性対策 普及啓発資料 ：IPA 独立行政法人 情報処理推進機構
の、
・安全なウェブサイトの作り方
・安全なSQLの呼び出し方

Comments

[kalon]

otnさん、いつもお世話になっております。ありがとうございます。
えっとー甘えさせていただきたいのですが、一番安全なSQLの呼び出し方は
'update twitter_users set comment = ? where name = ?', (name, comment)
ではないのでしょうか？セキュリティについては初心者もいいところなので、直接教えていただけると助かります。

Answer 3

「プリペアドステートメント」をキーワードに調べてみてください。

※Pythonが分かるわけではないのでヒントのみの回答失礼

Answer 4

以下のコードの書き方でシングルクオートがＤＢに登録されるようになりました。
この回答にマイナス評価を付ける方は、何が悪いのかもコメントに書いていただけると助かります。

import sqlite3


class Database:
    def __init__(self):
        self.dbpath = 'db.sqlite3'
        self.conn = sqlite3.connect(self.dbpath)
        self.c = self.conn.cursor()


db = Database()


def update_comment(name, comment='null'):
    db.c.execute("""update twitter_users set comment = ? where name = ?""", (comment, name))
    db.conn.commit()


update_comment('Jikkenndesu', "It's a bad day today, and I will go to Phillip's")

Answer 5

SQLでは文字列中のシングルクォートはシングルクォートを2つ重ねます。そのための関数を用意しますが、文字データ中にシングルクォートは入れないでください、って運用でカバーしているユーザーが多いです。

Comments

[kalon]

貴重な運用方法を教えてくださってありがとうございます。英語にも対応したいので、どうしてもシングルクオートを入れられるようにしたいのです。

[Orlofsky]

わたしにはシングルクォートを重ねる関数を教えられるほどの能力はありません。
SQLite はかなり簡易なデータベースですので性能の限界もあります。システムが大きくなる見込みがあればもっと性能の良いデータベースで作っておいた方が良いでしょう。
オラクル傘下に入ったMySQLはいつ無償のサービスが有料になったり、有料のサービスがいつ大幅に値上がりするかわからないので MySQLの フォーク https://ja.wikipedia.org/wiki/%E3%83%95%E3%82%A9%E3%83%BC%E3%82%AF_(%E3%82%BD%E3%83%95%E3%83%88%E3%82%A6%E3%82%A7%E3%82%A2%E9%96%8B%E7%99%BA) である MariaDB https://ja.wikipedia.org/wiki/MariaDB を選択する人も多いです。