pg gem のexec系メソッドの使い分け

Sinatraのメモアプリでpg gemを使う機会がありました。 DBにSQLを実行するexec ~ 系のメソッドの選択に迷ったので調べてみました。

結論

execexec_paramsexec_prepared の3つのメソッドは次のように使い分ける。

よく分からなかった静的プレースホルダと動的プレースホルダについても調べました。

exec

PG: The Ruby PostgreSQL Driver

  • PostgreSQLSQLを発行する。
  • 返り値
    • 成功: 各レコードが入った配列型のPGオブジェクト
    • 失敗: PG::Error
  • 引数にparamsを指定した場合は自動でexec_params が呼ばれる。
  • プレースホルダーを使ってSQLを組み立てる場合は明示的にexec_params を使った方が良い。
  • execasync_execエイリアスsync_exec とほとんど同じ。

exec_params

PG: The Ruby PostgreSQL Driver

  • パラメータを使ったプレースホルダーで組み立てたSQLPostgreSQLに実行する。
  • 返り値
    • 成功: PG::Result(exec と同じ)
    • 失敗: PG::Error
  • 引数に型指定が可能(指定しない場合はPostgreSQLが自動で判断する)。
  • 返り値のフォーマットを指定可能(テキスト、バイナリ)。
  • SQLインジェクション対策が自動で行われる。
    • 引用符付け、エスケープが自動で行われる。
    • 引数の文字列の中で1つのSQLしか実行できない。
  • async_exec_paramsエイリアス

exec_prepared

PG: The Ruby PostgreSQL Driver

  • paramsを使って動的に組み立てたSQLPostgreSQLに実行する。
  • 返り値
    • 成功: PG::Result(execと同じ)
    • 失敗: PG::Error
  • 型、フォーマットの指定が可能(exec_paramsと同じ)
  • async_exec_preparedエイリアス

まとめ

使い分けが分かってスッキリしました。 今回のプラクティスではSQLインジェクションの対策が終了条件に含まれているのでexec_params を使って実装してみようと思います。

参照

安全なSQLの呼び出し方

PostgreSQLのバルクインサートをRubyからPrepared Statementで実行 - Qiita

バルクインサートとは - 意味をわかりやすく - IT用語辞典 e-Words

プリペアドステートメントとは - 意味をわかりやすく - IT用語辞典 e-Words

SQLインジェクションを試してみる

SinatraのDB版のメモアプリの終了条件に「SQLインジェクションへの対策」が含まれていたのでSQLインジェクションできるのか実際に試してみました。

SQLインジェクションとは?

引数などのパラメタにSQL文を混ぜ込んでおき(インジェクション),プログラム内部でそのSQL文を実行させてしまう攻撃手法。 ログインを突破したり、データ・データベースの削除やコンテンツの改ざん(ウイルス・ワームの埋め込み等)、情報の詐取など、被害はさまざま。管理者権限を奪取されることもある。

参照: SQLインジェクションとは ウェブの人気・最新記事を集めました - はてな

原因

SQL内で展開するパラメータからSQLを発行できてしまうことが原因。

  • パラメータに渡す値を文字列ではなくSQLとして認識してしまう。
  • 文字列連結でSQLを組み立てる仕様になっている。

試してみる

試すのはSinatraで作成したメモアプリです。 DBはPostgreSQLを使っておりpg gemでデータ取得するような仕様になっています。 メモ詳細ページ(show)の表示処理を下記のようなコードで実装していました。

get '/memos/:id' do |id|
  @memo = @connection.exec("SELECT * FROM memos WHERE id = '#{id}'")
  erb :show
end

@connection.exec でDB接続の情報を元にSQLを発行します。 SQLを生成する際にブラウザから送信されたid をSELECT文の中で文字列展開しています。 この文字列展開する#{id} にはURLのmemos/ 以降の文字列が展開されるのでブラウザのアドレスバーに直接SQLを挿入できてしまう脆弱性があります。

例1) 投稿された全メモのタイトルを変更する

ブラウザのアドレスバーに下記を入力しアクセスすると全メモのタイトルがSQL injection! に変更されてしまいます。

http://localhost:4567/memos/';UPDATE memos SET title = 'SQL injection!'--

このアクセスにより2つのSQLが発行されます。

SELECT * FROM memos WHERE id = '';
UPDATE memos SET title = 'SQL injection!'--'

次のような理由でこのようなSQLが発行されてしまいます。

  • SQLの終端を表す;を挿入することででSQLを連結できる。
  • SQLのコメントを表す-- を末尾に追加することで不完全なSQLでも実行できる。

例2) 全メモを削除する

ブラウザのアドレスバーに下記を入力しアクセスすると全メモを削除できてしまいます。

http://localhost:4567/memos/';DELETE FROM memos--

このアクセスにより2つのSQLが発行されます。

SELECT * FROM memos WHERE id = '';
DELETE FROM memos--'

こちらも先程と同じように; によるSQLの連結と-- による不完全なSQLを実行できてしまうことが原因です。

まとめ

見事にSQLインジェクションできてしまいました... 原因は理解することができたので次はSQLインジェクションへの対策を行いたいと思います。

参照

安全なウェブサイトの作り方

安全なSQLの呼び出し方

SQLインジェクションとクエリの書き方について考える - Qiita

SQLインジェクション攻撃とは - goruchan’s blog

SQLファイルをコマンドラインから実行してテーブル定義する

Sinatraを使ったメモアプリのDB版を進めています。 テーブルを作成するDDL文を書いたSQLファイルを読み込み、PostgreSQLにテーブル定義したいと思ったので調べてみました。

結論

psql コマンドの-f オプションを使ってDDLを定義したSQLファイルを読み込んでテーブル定義する。

psql コマンドの-c-f オプション

コマンドラインから実行するpsql コマンドは次のようなオプションが用意されている。

# コマンドラインからSQLを実行(SQL文の末尾の;は不要)
$ psql -d DB名 -c "SQL" 

# コマンドラインからファイルを実行
$ psql -d DB名 -f ファイル名

注意点として-d オプションでDBを指定しない場合はデフォルトのDBに対してSQL、ファイルが実行されてしまいます。 デフォルト以外のDBに対してSQL、ファイルを実行したい場合は-d でDBを指定してください。

ファイルから SQL を読み込む (MySQL, PostgreSQL, SQLite3) - CUBE SUGAR CONTAINER

コマンドラインからDB作成、テーブル作成、初期データ投入までを行う

DDL文でのテーブル定義に加えて、初期データの投入まで同時に行います。 下記構成でDB、テーブルを作成します。

  • DB名: memo_app
  • テーブル名: memos
カラム名 データ型 制約
id CHAR(36) PRIMARY KEY
title VARCHAR(100) NOT NULL
content VARCHAR(255) なし

投入する初期データです。

id title content
bc9db029-408a-4f5b-bf49-5e5af9581532 memo1 this is memo1
c9e67c66-4219-411f-ab7f-625a7e5e4ceb memo2 this is memo2

下記のSQLコマンドラインから実行してDB作成、テーブル作成、初期データの投入まで行います。

CREATE DATABASE memo_app;
CREATE TABLE memos(id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, content VARCHAR(255), PRIMARY KEY (id));
INSERT INTO memos (id, title, content) VALUES ('bc9db029-408a-4f5b-bf49-5e5af9581532', 'memo1', 'this is memo1');
INSERT INTO memos (id, title, content) VALUES ('c9e67c66-4219-411f-ab7f-625a7e5e4ceb', 'memo2', 'this is memo2');

1. 初期化ファイルを作成

今回はSinatraを使ったアプリのカレントディレクトリにinitialize.sql という初期化ファイルを作成しました。 ファイルの実行内容は次の2つです。

  • テーブル作成
  • 初期データ投入
CREATE TABLE memos(id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, content VARCHAR(255), PRIMARY KEY (id));
INSERT INTO memos (id, title, content) VALUES ('bc9db029-408a-4f5b-bf49-5e5af9581532', 'memo1', 'this is memo1');
INSERT INTO memos (id, title, content) VALUES ('c9e67c66-4219-411f-ab7f-625a7e5e4ceb', 'memo2', 'this is memo2');

ちなみに、今回はsql という拡張子にしていますがテキストファイルなどでも大丈夫らしいです。

.sql【拡張子】とは|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

2. DB作成、初期化ファイルの実行

macPostgreSQLがインストール済みであることを前提にしています。僕はHomebrewでPostgreSQLをインストールしました。

次のコマンドの実行でDB作成、テーブル作成、初期データの投入までが完了します。

# アプリのディレクトリに移動
$ cd app_name

# memo_app DBを作成
$ psql -c "CREATE DATABASE memo_app"

# テーブル作成、初期データ投入用SQLファイルを実行
$ psql -d memo_app -f initialize.sql

DB作成、テーブル作成、初期データ投入の確認。

# DB作成を確認
❯ psql -l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 memo_app  | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
(5 rows)

# memo_app DBを指定して接続
❯ psql -d memo_app
psql (14.4)
Type "help" for help.

# テーブル作成を確認
memo_app=# \d memos;
                       Table "public.memos"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | character(36)          |           | not null |
 title   | character varying(100) |           | not null |
 content | character varying(255) |           |          |
Indexes:
    "memos_pkey" PRIMARY KEY, btree (id)

# 初期データ投入を確認
memo_app=# SELECT * FROM memos;
                  id                  | title |    content
--------------------------------------+-------+---------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 | this is memo1
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2 | this is memo2
(2 rows)

DB作成、テーブル作成、初期データ投入をコマンドラインから行うことができました。

できなかったこと

当初はCREATE DATABASEDDLファイルに含め1つのファイルでDB作成から初期データ投入まで行おうと考えていました。 しかし、次の理由から実現することができませんでした。

  • PostgreSQLにはMySQLUSE コマンドのようにDBを切り替えるSQLが用意されていない。
  • PostgreSQLはテーブル作成時にどのDBにテーブルを作成するのかも指定できない。

以上の2つの理由から1つのDB作成はpsql コマンドの-c オプションでCREATE DATABASE を実行し、テーブル作成以降のSQLコマンドを-f オプションでinitialize.sql を実行する方法を選択しました。

PostgreSQLとMySQLの基本的なコマンドを比較 - Qiita

もしかしたら見落としや他の方法があるかもしれないのでもうちょっと調べて見たいと思います。

まとめ

DDLがどんなものなのか理解する良い機会になりました。 また、コマンドラインからDBを作る方法も学べたのでどんどん使っていきたいと思います。 SinatraのメモアプリのDB化がんばります!

参照

ファイルから SQL を読み込む (MySQL, PostgreSQL, SQLite3) - CUBE SUGAR CONTAINER

.sql【拡張子】とは|「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

PostgreSQLとMySQLの基本的なコマンドを比較 - Qiita

SQLでCRUD処理を行う

SinatraのメモアプリをDB化するにあたってSQLCRUD処理を行う必要が出てきました。 SQLコマンドでCRUD処理を行う方法について改めて整理しました。

結論

下記のSQLコマンドででCRUD処理ができる。

  • Create: INSERT
  • Read: SELECT
  • Update: UPDATE
  • Delete: DELETE

CRUDとは - 意味をわかりやすく - IT用語辞典 e-Words

SQL入門 | PostgreSQLではじめるDB入門

DB、テーブルの作成

今回、DBはPostgreSQLを選択しました。 macHomebrewを使ってPostgreSQLのインストールを行いました。 PostgreSQLの基本操作はこちらの記事にまとめています。

下記構成でDB、テーブルを作成します。

  • DB名: memo_app
  • テーブル名: memos
カラム名 データ型 制約
id CHAR(36) PRIMARY KEY
title VARCHAR(100) NOT NULL
content VARCHAR(255) なし
# デフォルトユーザkarlley でデフォルトDBのkarlleyにログイン
$ psql (14.4)
Type "help" for help.

# memo_app DB作成 
karlley=# CREATE DATABASE memo_app;
CREATE DATABASE

# memo_app DBの作成を確認
karlley=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 memo_app  | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
(5 rows)

# memo_app DBに切替
karlley=# \c memo_app
You are now connected to database "memo_app" as user "karlley".

# memos テーブル作成
memo_app=# CREATE TABLE memos(id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, content VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE

# memos テーブルの作成を確認
memo_app=# \d memos
                       Table "public.memos"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | character(36)          |           | not null |
 title   | character varying(100) |           | not null |
 content | character varying(255) |           |          |
Indexes:
    "memos_pkey" PRIMARY KEY, btree (id)

スーパーユーザkarlley で作成したmemo_app DBに接続した状態で行います。

Create

CreateはINSERT コマンドで行います。

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

下記のレコードを追加します。

レコード1

  • id: bc9db029-408a-4f5b-bf49-5e5af9581532
  • title: memo1
  • content: this is memo1

レコード2

  • id: c9e67c66-4219-411f-ab7f-625a7e5e4ceb
  • title: memo2
  • content: this is memo2
# レコード追加
memo_app=# INSERT INTO memos (id, title, content) VALUES ('bc9db029-408a-4f5b-bf49-5e5af9581532', 'memo1', 'this is memo1');
INSERT 0 1
memo_app=# INSERT INTO memos (id, title, content) VALUES ('c9e67c66-4219-411f-ab7f-625a7e5e4ceb', 'memo2', 'this is memo2');
INSERT 0 1

# レコード確認
memo_app=# SELECT * FROM memos;
                  id                  | title |    content
--------------------------------------+-------+---------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 | this is memo1
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2 | this is memo2
(2 row)

Read

ReadはSELECT コマンドで行います。

SELECT カラム名 FROM テーブル名;

Createで追加したレコードを検索します。

# titleのみ検索
memo_app=# SELECT title FROM memos;
 title
-------
 memo1
 memo2
(2 rows)

# 全カラムを検索
memo_app=# SELECT * FROM memos;
                  id                  | title |    content
--------------------------------------+-------+---------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 | this is memo1
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2 | this is memo2
(2 rows)

Update

  • UpdateはUPDATE コマンドで行います。
  • WHERE の有無で更新対象を特定レコード/全レコードを指定できます。
# 特定レコード
UPDATE テーブル名 SET カラム名 = 変更する値 WHERE レコードを特定する条件式;

# 全レコード
UPDATE テーブル名 SET カラム名 = 変更する値;

title カラムの値がmemo1 のレコードの値をmemo1 update に更新します。

# 特定レコードの更新
memo_app=# UPDATE memos SET title = 'memo1 update' Where title = 'memo1';
UPDATE 1

# レコードの更新を確認
memo_app=# SELECT * FROM memos;
                  id                  |    title     |    content
--------------------------------------+--------------+---------------
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2        | this is memo2
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 update | this is memo1
(2 rows)

全レコードのcontent カラムの値をupdate content に更新します。

# 全レコード更新
memo_app=# UPDATE memos SET content = 'update content';
UPDATE 2

# レコードの更新を確認
memo_app=# SELECT * FROM memos;
                  id                  |    title     |    content
--------------------------------------+--------------+----------------
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2        | update content
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 update | update content
(2 rows)

Delete

  • DeleteはDELETE コマンドで行います。
  • WHERE の有無で削除対象を特定レコード/全レコードを指定できます。
  • 削除対象はレコード単位のため特定のカラムの値のみ削除することはできません。
# 特定のレコードの削除
DELETE FROM テーブル名 WHERE レコードを特定する条件式;

# 全レコード
DELETE FROM テーブル名;

title カラムの値がmemo2 のレコードのみ削除します。

# 特定レコードを削除
memo_app=# DELETE FROM memos WHERE title = 'memo2';
DELETE 1

# レコードの削除を確認
memo_app=# SELECT * FROM memos;
                  id                  |    title     |    content
--------------------------------------+--------------+----------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 update | update content
(1 row)

全カラムのレコードを削除します。

# 全レコードを削除
memo_app=# DELETE FROM memos;
DELETE 1

# 全レコードの削除を確認
memo_app=# SELECT * FROM memos;
 id | title | content
----+-------+---------
(0 rows)

感想

SQLでのCRUD処理に関する情報が整理されて良かったです。 実際にSinatraのメモアプリに組み込んでプラクティスを進めたいと思います。

参照

CRUDとは - 意味をわかりやすく - IT用語辞典 e-Words

SQL入門 | PostgreSQLではじめるDB入門

RubyのSecureRandom.uuidで生成した文字列の長さについて

FBCSinatraとDBを使ったメモアプリの開発に取り組んでいます。 DBに保存するレコードのRPIMARY KEYとしてSecureRandom.uuid で生成した文字列をCHAR型のカラムに保存しようと考えています。 最大文字列長をCHARに設定しようと思ったのですが、SecureRandom.uuid で生成した文字列が何文字か分からなかったので調べました。

結論

SecureRandom.uuid で生成した文字列の長さは36文字。

SecureRandom.uuid とは?

バージョン4のUUIDを生成するメソッド。

SecureRandom.uuid (Ruby 3.1 リファレンスマニュアル)

Ruby でID用のユニークな文字列を生成する方法 - karlley's tech blog

UUIDとは?

128ビットによる数値で16進数で表される文字列

RFC 4122 - A Universally Unique IDentifier (UUID) URN Namespace

文字数の内訳

UUIDは128ビット(16バイト)の値で、バージョン番号などに6ビットを用いるため、固有の識別情報は122ビットとなる。文字表記する際は先頭から4ビットごとに16進数の値(0~F)に変換し、「XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX」のように8桁-4桁-4桁-4桁-12桁に区切ってハイフンを挟んで記載する。

  • 識別情報の桁数: 8 + 4 + 4 + 4 + 12 = 32文字
  • ハイフン: 4文字
  • 文字数合計: 識別情報32文字 + ハイフン4文字 = 36文字

UUID(GUID / 汎用一意識別子)とは - 意味をわかりやすく - IT用語辞典 e-Words

感想

ちょっと気になっていたのでスッキリした! ビットやバイトという言葉が出てくると一気に分からなくなるので一度時間を作って振り返る必要がありそう。

参照

SecureRandom.uuid (Ruby 3.1 リファレンスマニュアル)

Ruby でID用のユニークな文字列を生成する方法 - karlley's tech blog

RFC 4122 - A Universally Unique IDentifier (UUID) URN Namespace

UUID(GUID / 汎用一意識別子)とは - 意味をわかりやすく - IT用語辞典 e-Words