未分類 | PostgresWeb - ポスグレウェブ

基本情報技術者試験　問３の解説

2017年（平成29年）秋、基本情報技術者試験の午後問３（DB）についての解説です。

説明のしやすさからEXCELファイルにて解説していますのでご了承ください。

内容はこんな感じです

「ダウンロード」ボタンよりダウンロードしてください。

基本情報技術者試験　問３の解説

2018年（平成30年）春、基本情報技術者試験の午後問３（DB）についての解説です。

説明のしやすさからEXCELファイルにて解説していますのでご了承ください。

内容はこんな感じです

「ダウンロード」ボタンよりダウンロードしてください。

基本情報技術者試験　問３の解説

2018年（平成30年）秋、基本情報技術者試験の午後問３（DB）についての解説です。

説明のしやすさからEXCELファイルにて解説していますのでご了承ください。

内容はこんな感じです

「ダウンロード」ボタンよりダウンロードしてください。

基本情報技術者試験　問３の解説

2019年（平成31年）春、基本情報技術者試験の午後問３（DB）についての解説です。

説明のしやすさからEXCELファイルにて解説していますのでご了承ください。

内容はこんな感じです

「ダウンロード」ボタンよりダウンロードしてください。

基本情報技術者試験　問３の解説

2019年（令和元年）秋、基本情報技術者試験の午後問３（DB）についての解説です。

説明のしやすさからEXCELファイルにて解説していますのでご了承ください。

内容はこんな感じです

「ダウンロード」ボタンよりダウンロードしてください。

○○攻撃まとめ

セキュリティの攻撃はいろいろな種類と名前があります。試験対策用に代表的なものに加え、〇〇攻撃とつくものを列挙しました。覚えているかの確認用等にも使用していただけるとうれしいです。不定期更新予定。

SQLインジェクション

Web サイトの入力エリアに脆弱性があり、攻撃者がSQL文を含んだ文字を入力しそのままSQL が実行されること。データベース情報の窃取、コンテンツの改ざんなどを行う。※インジェクションとは英語で「注入」「挿入」を表す。

発生しうる脅威

・データベース情報の窃取、コンテンツの改ざんなど

対策

・シングルクオーテーションやダブルクオーテーションといった記号や文字をエスケープする

その他

・実例としてはこのサイトが一番わかりやすかった　

XSS（クロスサイトスクリプティング）

Webアプリケーションの脆弱性を利用して悪意のあるデータを埋め込みスクリプトを実行させる攻撃手法。サイトをまたいでスクリプトが移転・実行されることが「クロスサイト」の名称の由来となっているが、現在では攻撃者が用意したスクリプトを標的サイトのものとして実行させられてしまう攻撃全般をクロスサイトスクリプティングと呼んでいる。

（画像出典）：IPA　安全なウェブサイトの作り方 - 1.5 クロスサイト・スクリプティング

発生しうる脅威

・本物サイト上に偽のページが表示され、フィッシング詐欺にあう

・ブラウザが保存しているCookieを取得される（セッションハイジャック）

・ウェブサイトの改ざん

対策

・入力値の制限

・サニタイジング（エスケープ）

より詳細な対策はIPAを参照（こちら）

その他

・イメージをつかむだけならこのサイトがおすすめ

XSS攻撃体験サイト

クリックジャッキング

クリックジャッキングとは、iframe（インラインフレーム）で表示させた透明なページを通常のウェブページの上にかぶせ、視覚的に騙してクリックを行わせる攻撃手法のこと。

（画像引用）：yamory Blog「悪意あるサイトにこっそり誘導クリックジャッキング」

発生しうる脅威

・ログイン後の利用者のみが利用可能なサービスの悪用（利用者が意図しない情報発信、利用者が意図しない退会処理等）

・ログイン後の利用者のみが編集可能な設定の変更（利用者情報の公開範囲の意図しない変更等）

対策

・HTTPレスポンスヘッダに、X-Frame-Optionsヘッダフィールドを出力し、他ドメインのサイトからのframe要素やiframe要素による読み込みを制限する。

・処理を実行する直前のページで再度パスワードの入力を求め、実行ページでは、再度入力されたパスワードが正しい場合のみ処理を実行する。

・重要な処理は、一連の操作をマウスのみで実行できないようにする。

CSRF

Cross Site Request Forgery（クロスサイトリクエストフォージェリ）。ユーザーがターゲットとなるウェブサイトへログインしている状態で、攻撃者が用意した罠サイトにアクセスを行う。その罠サイトでのリンククリック等により、ターゲットのウェブサイトに（偽のリクエストを送るなどして）ユーザーの予期しない処理を実行させること。例えば、ショッピングサイト○天にログインしている状態で、罠サイトのリンクをクリックしてしまうと、○天の最終決済や退会等の処理を実行する等。

（画像出典）：安全なウェブサイトの作り方 - 1.6 CSRF（クロスサイト・リクエスト・フォージェリ）

発生しうる脅威

・Webサイトにログイン後可能な処理をされる

　不正な送金、利用者が意図しない商品購入、利用者が意図しない退会処理

　各種設定の不正な変更（管理者画面、パスワード等）、掲示板への不適切な書き込み

対策

・Webサーバー側で、ページのhiddenに乱数（ワンタイムパスワード）を埋めこみそれを検証する

・処理を実行する直前のページで再度パスワードの入力を求め、実行ページでは、再度入力されたパスワードが正しい場合のみ処理を実行する

・重要な操作を行った際に、その旨を登録済みのメールアドレスに自動送信する（保護的な対策）

セッションハイジャック

何らかの手段でセッションIDやcookie情報を窃取し、本人に成り代わって通信を行うこと。

セッションIDを取得する手段

・セッションIDの推測

セッションIDの生成規則を割り出しセッションIDを推測する

・セッションIDの盗用

罠を仕掛ける、ネットワークを盗聴する等でセッションIDを盗む

・セッションIDを取得する方法ではなく、セッションIDを指定させる方法もある（セッションID固定化）

攻撃者が予め準備したセッションIDをユーザに送り込み、ユーザがそのセッションIDでログインする

発生しうる脅威

・ログイン後の利用者のみが利用可能なサービスの悪用や閲覧・改ざん

不正な送金、利用者が意図しない商品購入、利用者が意図しない退会処理等

各種設定の不正な変更（管理者画面、パスワード等）、掲示板への不適切な書き込み等

非公開の個人情報を不正閲覧、ウェブメールを不正閲覧、コミュニティ会員専用の掲示板を不正閲覧等

対策

・セッションIDを推測が困難なものにする

・セッションIDをURLパラメータに格納しない

・HTTPS通信で利用するCookieにはsecure属性を加える

・ログイン後に、新しくセッションを開始する

・ログイン後、既存のセッションIDとは別に秘密情報を発行し、ページ遷移ごとにその値を確認する

OSコマンドインジェクション

ソフトウェアなどのセキュリティホールが発見されてから、その情報公開や対策が講じられる前に、そのセキュリティホールを狙う攻撃のこと。

ゼロデイ攻撃

ソフトウェアなどのセキュリティホールが発見されてから、その情報公開や対策が講じられる前に、そのセキュリティホールを狙う攻撃のこと。

○○攻撃まとめ

分類はサイトによってまちまちのため保留にします。

忘れていないかをチェックするようにサイバー攻撃を列挙。

・中間者攻撃

・バッファオーバーフロー攻撃

・セッションハイジャック

・ SQLインジェクション

・ドライブバイダウンロード

・ DOS攻撃、DDOS攻撃

・ F5アタック

・ UDPフラッド攻撃（ランダム・ポート・フラッド攻撃）

・ Pingフラッド

・ SYNフラッド攻撃／FINフラッド攻撃

・ DNS Flood attacks（DNSフラッド攻撃）

・ DNSキャッシュポイズニング

・ DNS amp攻撃

・ ARPキャッシュポイズニング（ARPスプーフィング）

・ブルートフォースアタック、リバースブルートフォース攻撃

・パスワードリスト攻撃

・クレデンシャルスタッフィング攻撃

・辞書攻撃

・水飲み場型攻撃

・標的型攻撃

・ APT攻撃

・サプライチェーン攻撃

・キーロガー

・スミッシング

・ジュースジャッキング攻撃

・タイポスクワッティング（URLハイジャック）

・スロー攻撃

・ POODLE攻撃

・ LDAPインジェクション攻撃

・カミンスキー攻撃

・ディレクトリトラバーサル

・テンペスト攻撃

・サイドチャネル攻撃

・ガンブラー攻撃

・フィッシング詐欺

・ゼロクリック詐欺

・ランサムウェア

postgresql.confの解説【PostgreSQL】

設定ファイル（postgresql.conf）とは

・ postgresql.confはPostgreSQLについての様々な設定をするファイルです

・パラメータは1行に1つだけ設定できます

・ #はコメントに使われる記号で、それより右側がコメントとなります（途中からコメントアウトでOK）。

行頭に書けば、行全体がコメント行になります

・設定ファイルを変更した場合、ファイルの再読み込みにより有効になるパラメータと、データベースの再起動が必要になるパラメータがあります

設定ファイル（postgresql.conf）の場所

設定ファイルは、初期設定で『C:\Program Files\PostgreSQL\XX\data\postgresql.conf』にあります。

設定値の確認方法

設定値を確認するには「SHOW」を使用します。

--設定値を確認する
SHOW ALL;
SHOW 設定値名;

--設定値を確認する例
SHOW max_connections;       -- 100
SHOW shared_buffers;        -- 128MB

変更した設定値の反映させる

設定値を修正後、反映するには設定ファイルの再読み込みもしくは、PostgreSQLの再起動が必要になります。どちらが必要になるかは項目ごとに異なるため注意が必要です。

設定できる値について

・数値型の場合、kB（キロバイト）、MB（メガバイト）、GB（ギガバイト）の単位が指定可能

・時間型の場合、d（日）、h（時間）、min（分）、s（秒）、ms（ミリ秒）を指定可能。指定しない場合、項目ごとのデフォルトの単位が使用される。注意点として、msはミリ秒であり、マイクロ秒ではない。また、分の指定はmではなく、min。月単位の指定は不可。単位は大文字と小文字が区別されることも注意。

・ Boolean型（＝論理値型、論理値データ型と表現される場合あり）は、デフォルトのonもしくはoffのほか、trueもしくはfalse、あるいは、1もしくは0をとることができる

・コメントは「#」に続けて書く。「- -」や「/*　*/」は使用不可

設定項目について

それぞれの設定値について説明と補足です。

max_connections

説明

・データベースサーバーへの同時最大接続数を指定する（接続数が最大値に達するとエラーが発生）

・ max_connectionはその値にあわせて起動時にマシンリソースを確保するため、ただ大きく設定すればよいというものではない

・データベースクラスタ単位で設定するもので、データベース単位で設定で設定するものではない

・ superuser_reserved_connections（この下）はmax_connectionsに含まれるため、スーパーユーザーの数を含めて考慮が必要

・初期値は100

値の反映

・ PostgreSQLの再起動が必要（reloadでは不可）

superuser_reserved_connections

説明

・スーパーユーザーのために予約されている接続数を指定する。接続数がmax_connectionのパラメータを超えたときにスーパーユーザーが接続できなくなることを防ぐ。

・デフォルトは3。

値の反映

・ PostgreSQLの再起動が必要（reloadでは不可）

shared_buffers

説明

・メモリサイズを指定する。デフォルトで128MB

・ 1GB以上のRAMを載せた専用データベースサーバの場合、妥当な初期値はシステムメモリの25%

値の反映

・ PostgreSQLの再起動が必要（reloadでは不可）

port

・接続するポートを指定する。デフォルトは5432

log_destination

説明

・ log_destinatonはログの出力先を指定するパラメータ

・ stderr、syslog、eventlog、csvlogの４種類の値が指定できる

・ eventlogはWindows上で動作するPostgreSQLのみ指定ができる

・ csvlogは出力先ではなく、ログの形式を表す値です。これは出力先を表す値とカンマ区切りで組み合わせて指定する

・ csvlogを指定するとログの項目がカンマ区切りの書式（CSV）で出力される

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

log_checkpoints

説明

・チェックポイント処理が発生したことをログに出力。デフォルトはoff

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

log_connections

説明

・クライアントからサーバに接続されたこと（サーバへの接続試行）をログに出力。デフォルトはoff

値の反映

・ PostgreSQLの再起動が必要（reloadでは不可）

log_directory

説明

・ログの出力先となるディレクトリをlog_directoryで設定

・ディレクトリは絶対パスまたは、データベースクラスタディレクトリからの相対パスで指定する

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

log_disconnections

説明

・クライアントのサーバ接続終了をログに出力、デフォルト値はoff

値の反映

・ PostgreSQLの再起動が必要（reloadでは不可）

log_filename

・ログのファイル名

log_line_prefix

・ログメッセージの先頭行に追記する情報

log_rotation_age

説明

・分単位で指定。指定時間が経過すると新しいログファイルが生成される（1～1440）。デフォルトは24時間。

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

log_statement

説明

・指定した種類のSQLが発行されたことをログに出力、デフォルトは"none"

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

log_truncate_on_rotation

説明

・ログファイルのローテーション時に同名ファイルがあった時に上書きするか追加するかの設定。デフォルトはoff

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

temp_buffers

説明

・ソートなどセッションが一時的に使用するメモリを指定する

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

maintenance_work_mem

説明

・ VACUUMやCREATE INDEXなどの保守作業に使用されるメモリサイズを指定する

値の反映

・ PostgreSQLの再読み込みでOK（reloadでOK）

wal_buffers

説明

WAL（Write-Ahead Log）データ用に共有メモリで確保されるメモリサイズを指定する。PostgreSQLではバッファキャッシュの内容は、トランザクションがコミットされるかバッファがあふれるごとに書き出される。デフォルトの設定である-1は、shared_buffersの1/32の大きさとなる。

値の反映

・ PostgreSQLの再起動が必要（reloadでは不可）

CSVファイルからテーブルへデータを登録する、テーブルからCSVへファイル出力する【PostgreSQL】

COPYコマンドとは

COPY文はPostgreSQLによるSQLの独自拡張で、テーブルの内容をカンマ、タブ区切りのテキストファイルに出力したり、あるいはテキストファイルを読み込んでテーブルにコピーする時に使うコマンドです。

CSVからテーブルにデータをコピーする

CSVファイルの内容をテーブルにコピー（インポート）するコマンド例を紹介します。コマンドはpsqlで実行して下さい。※ CSVの各項目がダブルクォーテーションで囲まれている・いないどちらでも同じ動作をします。

# CSVの内容をテーブルにコピー（インポート）する構文
\copy テーブル名 from 'CSVファイルのフルパス'

# カンマ区切り、ヘッダ行なし
\copy test_table from 'CSVファイルのフルパス'

# カンマ区切り、ヘッダ行あり
\copy test_table from 'CSVファイルのフルパス' with csv header

# タブ区切り、ヘッダ行あり
\copy test_table from 'CSVファイルのフルパス' CSV DELIMITER E'\t' HEADER

# encoding
\copy test_table from 'CSVファイルのフルパス' WITH CSV HEADER encoding 'sjis'
\copy test_table from 'CSVファイルのフルパス' WITH CSV HEADER encoding 'UTF8'

補足：WITH CSV HEADER：CSVにヘッダ行があり、ヘッダーを読み込まないようにする

区切り文字はタブの時だけ指定（カンマ区切り用のオプションの「WITH CSV DELIMITER ‘,’」はなくてもOK）

テーブルからファイルにデータを出力する

テーブルの内容をファイル出力する（エクスポート）するコマンド例を紹介します。コマンドはpsqlで実行して下さい。

# CSVファイルを出力
\COPY テーブル名 TO 'CSVファイルパス' DELIMITER ',';
\COPY テーブル名 TO 'CSVファイルパス' WITH CSV;

# CSVファイルをヘッダを含めて出力
\COPY テーブル名 TO 'CSVファイルパス' WITH CSV HEADER;

# CSVファイルをダブルクォーテーションで囲む
\COPY テーブル名 TO 'CSVファイルパス' WITH CSV FORCE QUOTE *;

# 列を指定してCSV出力
\COPY テーブル名(列名1,列名2,…) TO 'CSVファイルパス' DELIMITER ',';

# SELECT文の結果を出力
\COPY (SELECT * FROM テーブル名 where 抽出条件 ) TO 'CSVファイルパス' DELIMITER ',';

# SELECT文の結果を出力しタブ区切りで出力
\COPY (SELECT * FROM テーブル名 where 抽出条件 ) TO 'CSVファイルパス' DELIMITER E'\t';

数値操作のまとめ【PostgreSQL】

【頻出】数値操作のまとめ

数値操作のうち、特に頻出のものをまとめました。

１．数値の書式設定

select to_char(12345.67, 'FM999,999.999'); --"12,345.67"  少数3桁ｶﾝﾏ区切り
select to_char(12345.67, 'FM999,999.000'); --"12,345.670" 少数3桁でｶﾝﾏ区切り

ここで詳しく説明しています → 数値を文字に変換する（to_char）

２．絶対値を取得

select * from abs(-5);  --5

ここで詳しく説明しています → 絶対値を取得する（ABS）

３．割り算の余りを取得

select * from mod(11,3);  --2（11÷3の余り）

ここで詳しく説明しています → 余りを計算する（mod）、偶数奇数によって処理を分ける

４．四捨五入

select * From round(123.45678,3);  --123.457（小数第3位四捨五入）
select * From trunc(123.45678,3);  --123.456（小数第3位切り捨て）
--切り上げを小数第1位までとする時：0.09を足して、小数第1位で切り捨てる

ここで詳しく説明しています → 四捨五入・切り上げ・切り捨て（Round）【自作関数】

５．ランダム値

select * from random(); --0.896430・・・
--範囲指定でのランダム値：最小値=1,最大値=10,000の時（値は適宜変更してください）
select round(( random() * (1 - 10000) )::numeric, 0) + 10000; --7000

ここで詳しく説明しています → ランダム値を生成する（random）

６．こちらもおすすめ

数値であるかのチェック（IsNumeric）

日付操作のまとめ【PostgreSQL】

【頻出】日付操作のまとめ

日付操作のうち、特に頻出のものをまとめました。

１．現在日時の取得

select * from current_date;      --"2020-06-11"
select * from current_time;      --"22:00:00.000000+09"
select * from now();             --"2020-06-11 22:00:00.000000+09"
select * from current_timestamp; --"2020-06-11 22:00:00.000000+09"
--now()とcurrent_timestampの違いはありません

ここで詳しく説明しています → 現在日時、システム日時の取得

２．日付の書式設定

select * from to_char(now(),'YYYY/MM/DD');  --2020/04/01
select * From to_char(now(), 'HH:MI:SS');   --10:34:56（12時間表記）
select * from to_char(now(), 'HH24:MI:SS'); --22:34:56（24時間表記）
select * from to_char(now(),'YYYY/MM/DD HH24:MI:SS'); --2020/04/01 22:34:56

ここで詳しく説明しています → 日時、時刻の書式設定をする（yyyymmdd形式）

３．月、週、日、時刻を加算する

select current_date + cast('5 months' as INTERVAL);  --"2020-11-12 00:00:00"
select current_date + cast('5 weeks' as INTERVAL);   --"2020-07-17 00:00:00"
select current_date + cast('5 days' as INTERVAL);    --"2020-06-17 00:00:00"
select current_date + cast('5 hours' as INTERVAL);   --"2020-06-12 05:00:00"
select current_date + cast('5 minutes' as INTERVAL); --"2020-06-12 00:05:00"
select current_date + cast('5 seconds' as INTERVAL); --"2020-06-12 00:00:05"

ここで詳しく説明しています → 日付の加算、週の加算、月の加算

４．曜日を取得する

select date_part('dow',current_date); --0:日、1:月、2:火、3:水、4:木、5:金、6:土

ここで詳しく説明しています → 曜日を取得する

５．月末の日を取得する

select date(date_trunc('month',current_date) + '1 month' + '-1 day') --2020/6/30

ここで詳しく説明しています → 月末日を取得する（LAST_DAY）

基本情報技術者試験 問３の解説

基本情報技術者試験 問３の解説

基本情報技術者試験 問３の解説

基本情報技術者試験 問３の解説

基本情報技術者試験 問３の解説

○○攻撃まとめ

SQLインジェクション

発生しうる脅威

対策

その他

XSS（クロスサイトスクリプティング）

発生しうる脅威

対策

その他

クリックジャッキング

発生しうる脅威

対策

CSRF

発生しうる脅威

対策

セッションハイジャック

セッションIDを取得する手段

発生しうる脅威

対策

OSコマンドインジェクション

ゼロデイ攻撃

○○攻撃まとめ

設定ファイル（postgresql.conf）とは

設定ファイル（postgresql.conf）の場所

設定値の確認方法

変更した設定値の反映させる

設定できる値について

設定項目について

max_connections

説明

値の反映

superuser_reserved_connections

説明

値の反映

shared_buffers

説明

値の反映

port

log_destination

説明

値の反映

log_checkpoints

説明

値の反映

log_connections

説明

値の反映

log_directory

説明

値の反映

log_disconnections

説明

値の反映

log_filename

log_line_prefix

log_rotation_age

説明

値の反映

log_statement

説明

値の反映

log_truncate_on_rotation

説明

値の反映

temp_buffers

説明

値の反映

maintenance_work_mem

説明

値の反映

wal_buffers

説明

値の反映

COPYコマンドとは

CSVからテーブルにデータをコピーする

基本情報技術者試験　問３の解説

基本情報技術者試験　問３の解説

基本情報技術者試験　問３の解説

基本情報技術者試験　問３の解説

基本情報技術者試験　問３の解説