環境

OS：CentOS 7.3
PHP：7.1.21
mariadb:10.1.22

概要

ＰＤＯで PDO::ATTR_EMULATE_PREPARES を false にすると、
（機能のひとつとして）セミコロンでつなげたＳＱＬ（複文）を禁止できるようですが、
コメント(--) の部分は複文として検知してくれないようです。

※ 以下、query() メソッド使用。

$sql .= "select * from table1;\n";
$sql .= "select * from table2;\n";

//検知してくれる ＯＫ
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select * from table2' at line 2

$sql .= "select * from table1;\n";
$sql .= "-- select * from table2;\n";

//検知しなかった？？？

$sql .= "select * from table1;\n";
$sql .= "-- select * from table2;\n";
$sql .= "select * from table3;\n";

//これは検知するけど３行目なんだ・・・
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select * from table3' at line 3

まとめ

複文禁止といっても「有効なＳＱＬの複文」を禁止するみたいで、
コメント行は有効なＳＱＬでないということなのでしょう。

コメント(--) を使ったＳＱＬインジェクションがありますが、
PDO::ATTR_EMULATE_PREPARES の設定だけでは不十分ということですね。

コメント使用の攻撃に対しては、
プリペアドステートメントを併用して防ぎましょう。

はじめに

様々な記事を参考にしながらなんとか導入できたので、
備忘録として残します。
もしおかしな点や、こうした方がいいなどありましたら
ご教授頂けますと幸いです。

開発環境

ruby 2.5.7
Rails 5.2.4.3
Vagrant 2.2.4
VirtualBox 6.0.14
OS: macOS Catalina
centos 7

流れ

1 vagrant上にMySQLを構築
2 既存アプリをSqliteからMySQLに変更
3 新規アプリをMySQLに設定
※基本的にはvagrant上で行うため、ssh接続しておいてください。

vagrant上にMySQLを構築

CentOS確認

まずは現在のCentOSを確認します。
確認方法はvagrantファイルにあるVagrantfileを確認します。

Vagrantfile

Vagrant.configure("2") do |config|
    GUEST_RUBY_VERSION = '2.5.7'
    config.vm.box = "centos/7"

...

今回はCentOSが7である前提で話を進めます。
CentOSとは仮想環境構築に使用する代表的なLinux系OSです。

MySQLのインストール（CentOS7用）

Vagrant＋Rails6＋MySQL 開発環境構築
こちらの記事を参考にまずはvagrant上にMySQLを構築します。

ターミナル

$ vagrant ssh
$ sudo yum -y install http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
$ sudo yum -y install mysql-community-server
$ mysqld --version

バーションが表示されればOKです。

自動起動設定後、MySQL起動

vagrant起動時に自動で起動するように設定します。
2行目はmysqld.service enabledになっていればOKです。

ターミナル

$ sudo systemctl enable mysqld.service
$ sudo systemctl list-unit-files -t service | grep mysqld
$ sudo systemctl start mysqld.service

MySQL初期設定(任意）

https://style.potepan.com/articles/19020.html
こちらの記事がわかりやすかったので、
$ mysql_secure_installation　を実行後、
この記事のMySQLの初期設定を実施しよう！から設定してください。

# rootユーザーにパスワードを設定(今回はrootパスワードを設定)
$ /usr/bin/mysqladmin -u root password 'root'

# セキュリティー関連の初期設定(ここでパスワードを聞かれると'root'とする)
$ mysql_secure_installation

設定後、下記を実行しパスワードを入力後、
mysql>
この表示になればOKです。

ターミナル

$ mysql -u root -p

既存アプリをSqliteからMySQLに変更

Railsでmysql2をインストールするときにハマったところ
[初学者]既存アプリのDBをMySQLに変更する方法
上記記事を参考に導入していきます。

railsアプリの作成、データベース確認

試しにscaffoldでpostテーブルを作成します。

ターミナル

$ rails new sam
$ cd sam
$ rails g scaffold post name:string

congig/database.ymlがこのような表記になっているかと思います。
初期設定ではsqlite3のデータベースをしようしています。

congig/database.yml

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

gemの導入

Gemfile

# Use sqlite3 as the database for Active Record
gem 'sqlite3'
↓
gem 'mysql2'

ターミナル

$ bundle install

エラーが出る場合は下記を実行するか、
Gemfileのgem 'mysql2'のバージョンを
gem 'mysql2', '~> 0.4.4'
に変更してみてください。

ターミナル

$ sudo yum install -y mysql-devel

データベース設定をMySQLに変更

congig/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password: 初期設定をした場合はパスワードを記述
  host: localhost

development:
  <<: *default
  database: sam_development # samはアプリ名です。

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: sam_test # samはアプリ名です。

production:
  <<: *default
  database: sample_production
  username: sample_app
  password: <%= ENV['SAMPLE_DATABASE_PASSWORD'] %>

下記でデータベースを作成。

ターミナル

$ bundle exec rake db:create
$ rails db:migrate

ターミナル

$ mysql -u root -p
$ show tables from sam_development;
+---------------------------+
| Tables_in_sam_development |
+---------------------------+
| ar_internal_metadata      |
| posts                     |
| schema_migrations         |
+---------------------------+
3 rows in set (0.00 sec)

このようになっていたら設定完了です。

新規アプリをMySQLに設定

こちらは既存アプリの変更より簡単に出来ます。

ターミナル

$ rails new sample -d mysql
$ cd sample
$ rails g scaffold post name:string

congig/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: 初期設定をした場合はパスワードを記述
  socket: /var/lib/mysql/mysql.sock

development:
  <<: *default
  database: sample_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: sample_test

# As with config/secrets.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password as a unix environment variable when you boot
# the app. Read http://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full rundown on how to provide these environment variables in a
# production deployment.
#
# On Heroku and other platform providers, you may have a full connection URL
# available as an environment variable. For example:
#
#   DATABASE_URL="mysql2://myuser:mypass@localhost/somedatabase"
#
# You can use this database configuration with:
#
#   production:
#     url: <%= ENV['DATABASE_URL'] %>
#
production:
  <<: *default
  database: sample_production
  username: sample
  password: <%= ENV['SAMPLE_DATABASE_PASSWORD'] %>

Gemfile

# Use mysql as the database for Active Record
gem 'mysql2', '>= 0.4.4', '< 0.6.0'

ターミナル

$ bundle exec rake db:create
$ rails db:migrate

もしAccess deniedのエラーが出た場合は、
下記のように一度ログイン後、再度上記を実行してください。

ターミナル

$ mysql -u root -p
exit

まとめ

初期設定を行うことによってAccess deniedで弾かれるこｔがありますが、
セキュリティー上仕方がないことかもしれません。
間違っている記述や方法がございましたらご教授頂けますと幸いです。

またtwitterではQiitaにはアップしていない技術や考え方もアップしていますので、
よければフォローして頂けると嬉しいです。
詳しくはこちら　https://twitter.com/japwork

SELECT結果がなんかおかしかった

テーブル(仮)

id	name
1	テスト
2	ﾃｽﾄ
3	てすと
3	test

SELECT * FROM test WHERE name = 'テスト';

id	name
1	テスト
2	ﾃｽﾄ
3	てすと

WHEREって完全一致だと思ってたので、ひらがな/カタカナが区別されていないことに動揺。
調べてみるとCollationの設定が関係しているらしい??
MySQLを使って2年目で初めてCollationというものを知りました(それまでは気にしたことがなかった)

Collationとは

各データベースにはデータベース文字セットとデータベース照合順序があります。

https://dev.mysql.com/doc/refman/5.6/ja/charset-database.html

ここでいうデータベース照合順序がCollationのことで、
データに含まれるの文字を比較するときにどの文字を区別してどの文字を同じものと見なすか？という設定のようなもの(らしい)
サーバレベル、データベースレベル、テーブルレベル、カラムレベルそれぞれで設定できる(らしい)

じゃあ今のCollationを確認してみます

Collationの確認方法

データベース

SELECT @@character_set_database, @@collation_database;

テーブル

show table status from [DB名];

カラム

SHOW FULL COLUMNS FROM test;

上記のテーブルはutf8mb4_unicode_ciになっていました。(だからSELECT結果がおかしく見えた)
区別したいのでutf8_general_ciに変更してみます

Collationの変更方法

データベース

ALTER DATABASE COLLATE [照合順序];

テーブル

ALTER TABLE [テーブル名] COLLATE [照合順序];

カラム

ALTER TABLE [テーブル名] MODIFY COLUMN [カラム名] [型] COLLATE [照合順序];

結果

SELECT * FROM test WHERE name = 'テスト';

id	name
1	テスト

欲しかった挙動になりました(とりあえずめでたしめでたし)
Collationについて理解しきれてないところがあるのでちゃんと調査・理解できたら後ほど追記などします

概要

超久しぶりにExcel VBAを触ることになったんですが、
その内容がMySQLからデータを取ってきて
指定のセルに入れるって処理で。

多分、しばらくこの手の話はこないだろうから、
備忘のために手順を残しておきます。

準備

ODBC Driver for MySQLをダウンロードします
①https://www.mysql.com/jp/products/connector/にアクセスします。
②ODBC Driver for MySQL (Connector/ODBC)のダウンロードボタンをクリックします。

③お使いのパソコン環境に合わせて、インストーラーのDownloadボタンをクリックします。

④以下のような画面が表示されるので、とりあえず「No thanks, just start my download」をクリックします。

⑤インストーラーがダウンロードされます。ダウンロード完了したら、インストーラーを起動します。

⑥インストーラーが起動したら、以下のような画面が表示されます。「Next」をクリックしてください。

⑦続いて以下のような画面が表示されます。「License Agreement」をよく読み、問題なければ「I accept・・・」を選択した上で、「Next」をクリックしてください。

⑧セットアップ種別の選択画面が表示されます。とりあえず「Typical」を選択し、「Next」をクリックしてください。

⑨インストールが始まります。

⑩インストールが完了したら、以下のような画面が表示されます。「Finish」をクリックしてインストーラーを終了します。

Excel VBA実装例

ここからは、VBAで処理を実装してみます。
Excelを起動し、開発者タブのVisual Basicをタッチします。

コードエディタが起動します。
ボタンをクリックしたら、MySQLのあるテーブルにSELECT構文のSQLを発行し、データを取得する例です。
※エラー処理は端折ってます。

Private Sub DataGetButton_Click()

    Dim AdoConnection As Object ' ADO コネクション
    Dim AdoRecordSet As Object  ' ADO レコードセット
    Dim SQL As String    ' SQL文

    ' ADOコネクションを作成します
    Set AdoConnection= CreateObject("ADODB.Connection")

    ' ODBC接続を行います
    AdoConnection.Open _
        "DRIVER={MySQL ODBC 8.0 Unicode Driver};" & _
        " SERVER=(servername);" & _
        " DATABASE=(databasename);" & _
        " UID=（username）;" & _
        " PWD=（password）;"

    ' SQL文を作成します
    SQL = "select tbl_aaa.ID, " & _
            "       tbl_aaa.name, " & _
            "       tbl_aaa.item_num, " & _
            "       tbl_bbb.item_name " & _
            "from tbl_aaa " & _
            "inner join tbl_bbb on tbl_bbb.ID = tbl_aaa.item_id "

    ' SQLを実行します
    Set AdoRecordSet = AdoConnection.Execute(SQL)

    ' 取得したレコードを適当なセルに表示していきます
    i = 0;
    Do Until AdoRecordSet .EOF
        Cells(i, 1) = AdoRecordSet!ID
        Cells(i, 2) = AdoRecordSet!name
        Cells(i, 3) = AdoRecordSet!item_name
        Cells(i, 4) = AdoRecordSet!item_num

        ' 次のレコードに移動
        AdoRecordSet .MoveNext
        i = i + 1
    Loop

End Sub

(servername)、(databasename)、(username)、(password)には、MySQLとの接続情報をセットしてください。

はじめに

モデル作ってマイグレートして、シークエルプロでDB確認したら

作ったつもりのデータベースがない！となったので、備忘録として対処法を載せときます。

解決法

こちらの記事を参考に対処したところ、解決に至りました。

やったこと

gemfileの修正

初歩的ミスですが、下画像の選択部分（gemfileのSQLite3）をコメントアウトします。

ターミナルにて

bundle install --without production

DBを作る必要があるので

bundle exec rake db:create

そして、これは必要あったのかな？既にやっていたので不要だったかも

rails db:migrate

できた

シークエルプロにて、データベースが確認できるようになりました。

目的

• DockerのMySQLにdumpファイルを読み込む方法をまとめる

実施環境

• ハードウェア環境

項目	情報
OS	macOS Catalina(10.15.5)
ハードウェア	MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)
プロセッサ	2 GHz クアッドコアIntel Core i5
メモリ	32 GB 3733 MHz LPDDR4
グラフィックス	Intel Iris Plus Graphics 1536 MB

• ソフトウェア環境

項目	情報	備考
dumpファイル作成MySQLバージョン	8.0.19 for osx10.13 on x86_64	Homwbrewを用いてこちらの方法で導入→Mac HomebrewでMySQLをインストールする
dumpファイル読み込みMySQLバージョン	8.0.19 for osx10.13 on x86_64	Homwbrewを用いてこちらの方法で導入→Mac HomebrewでMySQLをインストールする

前提条件

• dumpファイルが自分のPCのローカルに用意されていること。
• MySQLのコンテナが起動していること。

前提情報

• dumpファイルを読み込むDockerのMySQLは自分の先輩に用意していただいた環境のものとする。
  • Dockerで始めるLaravel講座 - Laravel編

概要

1. dumpファイルの移動
2. 読み込み
3. 確認

詳細

1. dumpファイルの移動

   1. dumpファイルが設置されているディレクトリまで$ cdコマンドで移動する。

   2. 下記コマンドを実行してdockerコンテナの中にdumpファイルをコピーする。

      $ docker cp dumpファイル名 コンテナ名:/コピー先のdumpファイル名
      

   3. 例えばローカルの~/Downloads直下にあるmy_dump.sqlをコンテナ名「docker_mysql_1」のルートディレクトリ直下にコピーしたい場合は下記のようになる。

      $ docker cp ~/Downloads/my_dump.sql docker_mysql_1:/my_dump.sql
      

2. 読み込み

   1. 下記コマンドを実行してMySQLのコンテナに入る。

      $ docker exec -it コンテナ名 bash
      

   2. 下記コマンドを実行してdumpファイルを読み込ませる。

      $ mysql -u MySQLユーザ名 -pMySQLのユーザ名に紐付いたパスワード dumpファイルを読み込むDB名 < dumpファイル名
      

3. 確認

   1. MySQLのコンテナ内で下記コマンドを実行してMySQLにログインする。

      $ mysql -u root -p
      

   2. 下記SQLを実行してdumpファイルの内容が正常に読み込まれテーブルなどが存在していることを確認する。

      use dumpを読み込んだDB名;
      show tables;