step9 SQLに触れてみる

[smptmhr]

レコード作成

rails console上でタスクを作成 入力

task = Task.create(name: "create in console", start_date:"2022-05-25",
 necessary_days: 5, progress: 1, priority: 2)

=>
 INSERT INTO "tasks" ("name", "description", "start_date", "necessary_days", 
"progress", "priority", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id" 

INSERT文 : どのようなデータをどのテーブルに登録するか

文法

BEGIN

BEGINはトランザクションブロックを初期化する。

INSERT分の基本構文

INSERT INTO テーブル名 (カラム名1,カラム名2,...) VALUES(値1,値2,...)

VALUES ($1, $2, $3, $4, $5, $6, $7, $8)

これはおそらく

[["name", "create in console"], ...
,["updated_at", "2022-05-29 03:33:11.592497"]]

に対応しているように見える、調べたけどあまり分からなかった。

RETURNING "id"

これによって#<Task:0x00000001158bc660が返ってきている

COMMIT

COMMIT文を使用すると、現行のトランザクションを終了し、トランザクションで実行したすべての変更を確定できます。

indexページ

タスクが作成されていることを確認

[smptmhr]

レコード更新

rails console 上でタスクを更新

入力

task.update(name: "updated in console")

=> UPDATE "tasks" SET "name" = $1, "updated_at" = $2 WHERE "tasks"."id" = $3  
[["name", "updated in console"], ["updated_at", "2022-05-26 02:50:38.750979"], ["id", 45]]                                                       

文法

UPDATE テーブル名 SET カラム名1 = 値1 ( , カラム名2 = 値2 , ...) WHERE 条件;

WHERE 条件がないと全レコードが更新される。

結果

[smptmhr]

レコードの取得

全件取得

Task.all
=> SELECT `tasks`.* FROM `tasks`

SELECT : 探したい列を指定 FROM : どのテーブルから探すか指定

• : ワイルドーカード

主キーでレコードを検索(単数)

Task.find(1)
=> SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]] 

LMIT : 取得するレコードの上限数

主キーでレコードを検索(複数)

Task.find([1,2,3])
SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" IN ($1, $2, $3)  [["id", 1], ["id", 2], ["id", 3]]  

IN : or句を一つにまとめて書ける この二つは同じ意味

SELECT "tasks".* FROM "tasks" 
WHERE "tasks"."id"= 1
OR "tasks"."id"= 2
OR "tasks"."id"= 3

SELECT "tasks".* FROM "tasks" 
WHERE "tasks"."id" IN (1, 2, 3)

NOT INというものもあるが、これは全レコードを見に行き、重い処理になりがちなので注意。

[smptmhr]

レコード削除

rails console 上でタスクを削除

task.delete
 =>DELETE FROM "tasks" WHERE "tasks"."id" = $1  [["id", 68]]

文法

DELETE : 対象のレコードを削除する

疑問点

レコード削除後にtask.valid?をすると問題なくレコードとtrueが返ってくるのが謎。 indexページではタスクが削除されている。

[smptmhr]

SQLインジェクション

SQLインジェクションとは

アプリにSQLとして意味のある文字列を送り、データベースに不正にアクセスする攻撃。サーバー側では正常なSQLとして実行されてしまう。

SQLインジェクションの例

例えば

Project.where("name = '#{params[:name]}'")

というコードに対して' OR 1 --という文字列を送ると以下のSQLが実行される。

SELECT * FROM projects WHERE name = '' OR 1 --'

このSQLによってprojectsテーブルから全てのレコードが取り出されてしまう。

RailsでのSQLインジェクション対策

Ruby on Railsには、特殊なSQL文字をフィルタするしくみが組み込まれており、「'」「"」「NULL」「改行」をエスケープします。Model.find(id)やModel.findby*(引数)といったクエリでは自動的にこの対策が適用されます。ただし、SQLフラグメント、特に条件フラグメント（where("...")）、connection.execute()またはModel.find_by_sql()メソッドについては手動でエスケープする必要があります。

Rails セキュリティガイド - Railsガイド

条件オプションに文字列を直接渡すのではなく、引数のように渡すことで無害化できる。

Model.where("zip_code = ? AND quantity >= ?", entered_zip_code, entered_quantity).first

[KessaPassa]

口頭でsqlインジェクションの説明もらったのでOK