karlley's tech blog

学習メモや記録とか

ヒアドキュメントでSQLを読み易くする

Sinatraを使ったメモアプリを開発中です。 rubyファイル内の長くて読みにくいSQLを読み易く記述する方法を調べました。

結論

ヒアドキュメントを使うと長いSQLの可読性が上がるかもしれません。 rubyファイル内のSQLは次のように書き換えることができます。

# SQLを実行するメソッド
def excute(query)
  connection = PG::Connect.new(dbname: memo_app)
  connection.exec(query)
end

# メソッド呼び出し
excute(INSERT INTO memos (id, title, content) VALUES ('#{params[:id]}', '#{params[:title]}', '#{params[:content]}'))

上のメソッド呼び出し部のSQLをヒアドキュメントで書き換えると次のようになります。

excute(<<~SQL)
  INSERT INTO memos (id, title, content)
  VALUES ('#{params[:id]}', '#{params[:title]}', '#{params[:content]}')
SQL

SQLにインデントが追加され、改行が加わることで読み易くなったと個人的には感じます。 上のヒアドキュメントを使った書き方はちょっと特殊なのでポイント毎に解説します。

ヒアドキュメントとは?

ヒアドキュメントは<<識別子 を含む行の次の行から識別子 だけの行の直前までを文字列とする行指向のリテラルです。

print <<EOS      # 識別子 EOS までがリテラルになる
  the string
  next line
EOS

これは以下と同じです。

print "  the string\n  next line\n"

参照: リテラル (Ruby 3.1 リファレンスマニュアル)

ポイント1: ヒアドキュメントの文字列部をインデントする

開始ラベルをチルダを使って<<~識別子 とすることで文字列部をインデントすることができます。

  • インデントの深さは最もインデントが少ない行が基準になる。
  • 出力時には文字列部先頭の空白は除去される。

チルダの有無での出力結果を比較すると以下のようになります。

# チルダ無し
print <<EOS
  the string
    next line
EOS

# 出力結果
  the string
    next line
# チルダ有り
print <<~EOS
  the string
    next line
EOS

# 出力結果
the string
  next line

最もインデントが少ない行が基準になり、先頭の空白のが取り除かれています。

ポイント2: ヒアドキュメントをメソッドの引数に使う

メソッドの引数としてヒアドキュメントを使うことができます。 識別子(<<~EOS)が入った引数の丸括弧を先に閉じる点がかなり特殊だと思います。

def puts_text(s)
  puts s
end

# メソッドの引数をヒアドキュメントで指定
puts_text(<<~EOS)
  This is
    TEXT!
EOS

# 出力結果
This is
  TEXT!

丸括弧を先に閉じない場合でも書けるようですがrubocopでは先に閉じるパターンを推奨しています。

まとめ

ヒアドキュメントは使う機会が少なく殆ど覚えていなかったので良い復習になったので良かった。 少しでも読み易くて綺麗なコードが書けるようになりたい。

参照

Rubyのヒアドキュメントの書き方いろいろ - Hack Your Design!

Rubyのヒアドキュメント 4パターンのまとめ -- ぺけみさお

rubocop/ruby-style-guide: A community-driven Ruby coding style guide

pgでPostgreSQLに接続する

SinatraのメモアプリをDB化するにあたりpg gemを使いPostgreSQLに接続しSQLを実行する方法を調べました。

結論

次の手順でpg gemでPostgreSQLへ接続しSQLを実行できる。

  1. PostgreSQLに接続するためのPGオブジェクトを作成。
  2. 作成したPGオブジェクトからSQLを実行するメソッドを呼び出す。
require 'pg'

# PGオブジェクト作成、SQL実行
connection = PG::Connection.new(dbname: 'memo_app')
all_memos = connection.exec('SELECT * FROM memos')

# DBから取得した結果を出力
all_memos.each do |memo|
  puts "id: #{memo['id']}, title: #{memo['title']}, content: #{memo['content']}"
end

# 実行結果
id: bffcbdbf-6191-4057-a8df-d274573a728a, title: test_memo, content: text_content
  • SQLを実行して取得したレコードは配列型になっています。
  • require 'pg' でgemを読み込むのを忘れずに!

pgとは?

RubyPostgreSQLを使うためのGem。 DB作成後、PGオブジェクト作成時にdbname を指定しただけで接続できたので驚いた。

ged/ruby-pg: A PostgreSQL client library for Ruby

PostgreSQLへの接続

Connection.new を使って接続用のPGオブジェクトを作成します。

PG: The Ruby PostgreSQL Driver

# PGオブジェクトを作成
connection = PG::Connection.new(dbname: 'memo_app')

今回はConnection.new への引数はdbname しか指定していませんが、porthost などを渡すこともできるようです。

ちなみに公式のサンプルにあるPG.connectPG::Connection.newエイリアスのようです。

exec系メソッド

DBににSQLを実行するメソッドです。使用用途別に複数のメソッドが用意されているので必要に応じて選択してください。

pg gem のexec系メソッドの使い分け - karlley's tech blog

今回のサンプルはシンプルにSQLを実行するメソッドであるexec を使いました。

まとめ

PostgreSQL接続に関する情報が上手く整理できていなかったのでスッキリして良かったです。 プラクティスも終盤なのでがんばります。

参照

ged/ruby-pg: A PostgreSQL client library for Ruby

PG: The Ruby PostgreSQL Driver

pg gem のexec系メソッドの使い分け - karlley's tech blog

PostgreSQLのSQLインジェクションの対策

前回の記事ではPostgreSQLを使ったSinatraのメモアプリにSQLインジェクション攻撃ができることを確認したので、その対策について調べてみました。

環境

今回紹介するSQLインジェクションの対策は下記の環境で行いました。

結論

SQLインジェクションの対策には次の2つを行う。

  1. プレースホルダー(バインド機構)でSQL文を組み立てる。
  2. パラメータを正しくリテラルとして展開する。
  3. 文字列リテラル: エスケープすべき文字をエスケープする。
  4. 数値リテラル: 数値以外の文字を混入させない。

対策1. プレースホルダー(バインド機構)でSQL文を組み立てる

プレースホルダー(バインド機構)については以前の記事にまとめていますので参考にしてください。

変数を使ってSQL内のパラメータへ値を機械的に代入しSQL文を組み立てることでSQLの書き換えを防止することができます。DBや言語の種類によってはプレースホルダーでのSQL組み立てをサポートしてくれるライブラリやメソッドが用意されている場合もあります。

例) フォーム画面から送信される値params を使ってmemos テーブルにレコードを作成する。

-- NG
INSERT INTO memos (id, title, content) VALUES ('params[:id]', 'params[:title]', 'params[:content]')

-- OK
INSERT INTO memos (id, title, content) VALUES ($1, $2, $3)

DBに送信する値params を変数$ に置き換えています。ここの変数名はDBの種類等で異なるようです。 置き換えた変数$1$2$3 に送信する値params[:id]params[:title]params[:content]機械的に代入(バインド)することでSQLの書き換えを防止します。 バインド処理はSQLではできないのでpg gemのexec_params メソッドを使います。詳細は後述します。

対策2. パラメータを正しくリテラルとして展開する

SQL組み立てに使用するパラメータを正しくリテラルとして展開しないと意図しないSQLの実行を許してしまいます。 リテラル展開時にエスケープする文字はDBの種類や設定によって異なるので、DBやライブラリに用意されている展開用のメソッドを使うのが一般的なようです。

pg gemのexec_params メソッドはこの点も考慮されているようです(pg gemのexec系メソッドの使い分け)。

対策例

上記2点を踏まえた上での対策例を紹介します。

例) フォーム画面から送信される値params を使ってmemos テーブルにレコードを作成する。

require 'pg'

# paramsをバインド処理後にSQLを実行
def excute(query, params)
  connection = PG::Connection.new(dbname: memo_app)
  connection.exec_params(query, params)
end

# 元となるSQL、送信されたパラメータの配列を使いexcuteメソッドを実行
def create_memo(id, title, content)
  query = "INSERT INTO memos (id, title, content) VALUES ($1, $2, $3)"
  params = [params[:id], params[:title], params[:content]]
  excute(query, params)
end

create_memo メソッドで生成されるSQLは次のようなSQLです。

INSERT INTO #{TABLE_NAME} (id, title, content) VALUES ('params[:id]', 'params[:title]', 'params[:content]')

exec_params メソッドの次のような構文になっています。 オプションで型やデータ型を指定できるようですが割愛します。

exec_params('SQL文', [展開する値の配列])

この方法で「プレースホルダーでSQL文を組み立てる」、「パラメータを正しくリテラルとして展開する」という2点をクリアすることができました。前回の記事と同じ方法でSQLインジェクションを試してみましたが防止できていることを確認できました。

まとめ

時間は掛かりましたがSQLインジェクションの原因と対策について学ぶことができて良かったです。 セキュリティは難しいですがブラックボックスな部分が垣間見れるので面白みも感じることができるので楽しかったです。

参照

SQLインジェクションを試してみる - karlley's tech blog

静的プレースホルダと動的プレースホルダについて - karlley's tech blog

pg gem のexec系メソッドの使い分け - karlley's tech blog

安全なSQLの呼び出し方

PostgreSQL: The world's most advanced open source database

Sinatra: README (Japanese)

ged/ruby-pg: A PostgreSQL client library for Ruby

PostgreSQLの文字型の種類について

PostgreSQLのDB作成時の型指定に迷ったので調べました。

結論

PostgreSQLには3つの文字型がある。

CHAR

  • 固定長文字列
  • ()で最大文字列長を指定
  • 最大文字列に満たない場合は半角スペースが末尾に入る
  • 大文字/小文字を区別する

VARCHAR

  • 可変長文字列
  • ()で最大文字列長を指定
  • 最大文字列に満たない場合でも半角スペースは入らない
  • 大文字/小文字を区別する

TEXT

  • 可変長文字列
  • 最大文字列長は指定不可

まとめ

3つの違いについては理解できたけど使い分けについてはモヤモヤする...実際に色々なDBに触って知っていくしかなさそうです。

参照

8.3. 文字型

静的プレースホルダと動的プレースホルダについて

静的プレースホルダと動的プレースホルダの違いが分からなかったので調べました。

結論

SQLの組み立てには2つの方法がある。

プレースホルダーとは?

プレースホルダによる組み立てとは、パラメータ部分を「?」などの記号で示しておき、後に、そこへ実際 の値を機械的な処理で割り当てる方法です。

パラメータ部分を示す記号「?」のことをプレースホルダと呼び、そこへ実際の値を割り当てるこ とを「バインドする」と呼びます。プレースホルダのことを「バインド変数」と呼ぶこともあります。

静的プレースホルダ

プレースホルダのままの SQL 文をデータベースエンジン側にあらかじめ送信して、実行前に、 SQL 文の構文解析などの準備をしておく方式です。SQL 実行の段階で、実際のパラメータの値をデータ ベースエンジン側に送信し、データベースエンジン側がバインド処理します。

  • Prepared Statement
  • SQL組み立てはDB側で行う。
  • SQLがあとから変化しないのでセキュリティ面では一番安全。

動的プレースホルダ

動的プレースホルダは準備された文(Prepared Statement)とは異なり、プレースホルダを利用するもの の、パラメータのバインド処理をデータベースエンジン側で行うのではなく、アプリケーション側のライブラ リ内で実行する方式です。

まとめ

整理して初めて自分が行おうとしていた実装方法が「動的プレースホルダ」だったのだと理解できました。 静的プレースホルダはなんだか難しそうな印象...また必要になったら調べてみます。

参照

安全なSQLの呼び出し方

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