pgを使ってPostgreSQLを操作する & SQLインジェクション

前に書いたSinatraでメモアプリを作成する課題について。

ud-ike.hatenablog.com

最初はDBを使わずファイルにデータを保存する形で作成して、その次の課題でDBを使うように修正します。今回はDB版の話。

環境：macOS Catalina / Ruby 2.7.1

DB版の要件
PostgreSQLとruby-pgをインストール
- よく使ったコマンド
pgでSQLを操作する
dotenvを使ってDBへの接続情報を隠す
SQLインジェクションという脆弱性

DB版の要件

DBはPostgreSQLを使う
DBアクセスのライブラリにはActiveRecordを使わずpgを使う

pgとはDBへアクセスするためのgemです。 ActiveRecordを使うのが一般的なようだが、これをやる前にシンプルなpgを使おうってことみたい。Railsやる前にSinatraやるのと同様に。

PostgreSQLとruby-pgをインストール

MacにHomebrewでPostgreSQLをインストールする。

% brew install postgresql

バージョン確認↓

% psql -V  
psql (PostgreSQL) 12.4

操作用のユーザとしてpostgresを作成した。

postgres=# create user postgres with SUPERUSER;
CREATE ROLE

参考：[macOS High Sierra][Homebrew] PostgreSQL のインストールからDB作成まで - Qiita

pgをインストール。

% gem install pg

データベース名：memo

テーブル名：Memos

で作成し、適当にテストデータを登録する。

postgres=# CREATE DATABASE memo owner=postgres;
CREATE DATABASE

postgres=# \c memo 
You are now connected to database "memo" as user "postgres".
memo=# CREATE TABLE Memos
memo-# (id serial,
memo(# title VARCHAR(100) NOT NULL,
memo(# content VARCHAR(1000) ,
memo(# update_at TIMESTAMP NOT NULL,
memo(# PRIMARY KEY (id));
CREATE TABLE

よく使ったコマンド

psql -Upostgres：ユーザpostgresでログイン

\c データベース名：指定したデータベースに接続

\r：タイプミスなどで途中やめしたいとき

pgでSQLを操作する

参考：pg の使い方の簡単な説明 - 君の瞳はまるでルビー - Ruby 関連まとめサイト

SELECTできた！

require 'pg'

# データベースに接続する
connection = PG::connect(host: "localhost", user: "postgres", password: "xxxxxx", dbname: "memo")

begin
  # PostgreSQLを操作する
  results = connection.exec("SELECT * FROM Memos;")
  results.each do |r|
    p r
  end

ensure
  # データベースへのコネクションを切断する
  connection.finish
end

dotenvを使ってDBへの接続情報を隠す

上のような書き方をすると、パスワードが丸見えになってしまう。

dotenvというgemを使うことにした。

インストールして.envという名前のファイルを作成する↓

DB_HOST='localhost'
DB_USER='postgres'
DB_PASSWORD='xxxxxx'
DB_NAME='memo'

先ほどのファイルの最初の部分を以下のように書き換えると接続できた。

require 'pg'
require 'dotenv/load'

connection = PG::connect(
  host: ENV["DB_HOST"],
  user: ENV["DB_USER"],
  password: ENV["DB_PASSWORD"],
  dbname: ENV["DB_NAME"]
)

参考：【4回目（前編）】Railsに挫折中の人が、Ruby/Sinatraから再入門してみた（全7回）｜tatsugon｜note

SQLインジェクションという脆弱性

インジェクション=注入という意味。

例えばSELECT id, title, content FROM Memos WHERE id = #{id};というようにSQL文に値を直接代入すると、データを削除されたり顧客情報が流出したりする被害が出る可能性がある。仕組みは調べるとなんとな〜く理解できる。

参考：SQLインジェクション | 用語集 | CyberSecurityTIMES

例えば、新しいメモのデータをINSERTするときにはこのように書けばできた。

connection.exec("INSERT INTO Memos (title, content, update_at) VALUES ($1, $2, current_timestamp);", [title, content])

ちなみにcurrent_timestampはPostgreSQLで用意されているデータ型です。

Hit the books!!

プログラミング学習記録