Chapter 04

クエリ

Thirosue
Thirosue
2021.05.16に更新
このチャプターの目次
  1. クエリ
    1. 前提
    2. 事前準備
    3. 単純な検索（1件検索）
    4. 単純な検索（リスト検索）
    5. Join検索（ユーザ詳細）
    6. Join検索（投稿一覧）
    7. Native Query ［パラメータ埋め込み］
    8. SQLインジェクション確認（Native Query ［パラメータ埋め込み］）
    9. Native Query ［パラメータ指定］
    10. SQLインジェクション確認（Native Query ［パラメータ指定］ ）

クエリ

クエリのさわりを確認していきます。
さらに突っ込んだ内容は、Prismaの公式ドキュメントを確認してください。

前提

前チャプターの最後の手順（初期データ投入）を実施している前提（ユーザ2件、記事101件）で進める

% npx prisma db seed --preview-feature

事前準備

prisma/index.tsファイルを作成し、以下の内容を記載する

prisma/index.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // TODO ここに処理を記載する
}

main()
  .catch((e) => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

本チャプターでは、上記のファイルのTODO箇所に処理を記載し、Prismaでのクエリ発行機能を確認していく

単純な検索（1件検索）

ユーザを1件取得する

  const alice = await prisma.user.findUnique({
      where: { email: 'alice@example.com' }
  })

  console.log(alice)
  • クエリ発行
% npx ts-node prisma/index.ts
{ id: 1, email: 'alice@example.com', name: 'Alice', tel: null }

発行されたSQLは以下のとおり

2021-05-16 11:09:52.395 UTC [3070] LOG:  execute s0: SELECT "postgres"."User"."id", "postgres"."User"."email", "postgres"."User"."name", "postgres"."User"."tel" FROM "postgres"."User" WHERE "postgres"."User"."email" = $1 LIMIT $2 OFFSET $3
2021-05-16 11:09:52.395 UTC [3070] DETAIL:  parameters: $1 = 'alice@example.com', $2 = '1', $3 = '0'

プリペアドステートメント（Prepared statements）で処理されていることが確認できる。

単純な検索（リスト検索）

ユーザを複数取得する

  const users = await prisma.user.findMany()

  console.log(users)
  • クエリ発行
% npx ts-node prisma/index.ts
[
  { id: 1, email: 'alice@example.com', name: 'Alice', tel: null },
  { id: 2, email: 'bob@example.com', name: 'Bob', tel: null }
]

発行されたSQLは以下のとおり

2021-05-16 11:25:29.514 UTC [3103] LOG:  execute s0: SELECT "postgres"."User"."id", "postgres"."User"."email", "postgres"."User"."name", "postgres"."User"."tel" FROM "postgres"."User" WHERE 1=1 OFFSET $1
2021-05-16 11:25:29.514 UTC [3103] DETAIL:  parameters: $1 = '0'

Join検索（ユーザ詳細）

Bobに紐づいた投稿を取得する

  const bob = await prisma.user.findUnique({
    where: {email: 'bob@example.com'},
    include: { posts: true }
  })

  console.log(bob)
  • クエリ発行
% npx ts-node prisma/index.ts
{
  id: 2,
  email: 'bob@example.com',
  name: 'Bob',
  tel: null,
  posts: [
    {
      id: 101,
      title: 'Check out Prisma with Next.js',
      content: null,
      published: false,
      authorId: 2
    }
  ]
}

発行されたSQLは以下のとおり

joinではなく、クエリを2回発行していることが確認できる（ユーザを特定したのち、IDで再検索）

2021-05-16 11:31:32.453 UTC [3117] LOG:  execute s0: SELECT "postgres"."User"."id", "postgres"."User"."email", "postgres"."User"."name", "postgres"."User"."tel" FROM "postgres"."User" WHERE "postgres"."User"."email" = $1 LIMIT $2 OFFSET $3
2021-05-16 11:31:32.453 UTC [3117] DETAIL:  parameters: $1 = 'bob@example.com', $2 = '1', $3 = '0'
2021-05-16 11:31:32.457 UTC [3117] LOG:  execute s1: SELECT "postgres"."Post"."id", "postgres"."Post"."title", "postgres"."Post"."content", "postgres"."Post"."published", "postgres"."Post"."authorId" FROM "postgres"."Post" WHERE "postgres"."Post"."authorId" IN ($1) OFFSET $2
2021-05-16 11:31:32.457 UTC [3117] DETAIL:  parameters: $1 = '2', $2 = '0'

Join検索（投稿一覧）

投稿一覧（ページング）を取得する

タイトルをキーワードで絞り込み、取得件数を絞り込む

    const posts = await prisma.post.findMany({
        where: {
            title: {
                startsWith: 'title'
            }
        },
        take: 5,
        orderBy: {
            id: 'asc'
        },
        include: { author : true }
    })

    console.log(posts)
  • クエリ発行
% npx ts-node prisma/index.ts
[
  {
    id: 1,
    title: 'title 1',
    content: null,
    published: false,
    authorId: 1,
    author: { id: 1, email: 'alice@example.com', name: 'Alice', tel: null }
  },
  {
    id: 2,
    title: 'title 2',
    content: null,
    published: false,
    authorId: 1,
    author: { id: 1, email: 'alice@example.com', name: 'Alice', tel: null }
  },
  {
    id: 3,
    title: 'title 3',
    content: null,
    published: false,
    authorId: 1,
    author: { id: 1, email: 'alice@example.com', name: 'Alice', tel: null }
  },
  {
    id: 4,
    title: 'title 4',
    content: null,
    published: false,
    authorId: 1,
    author: { id: 1, email: 'alice@example.com', name: 'Alice', tel: null }
  },
  {
    id: 5,
    title: 'title 5',
    content: null,
    published: false,
    authorId: 1,
    author: { id: 1, email: 'alice@example.com', name: 'Alice', tel: null }
  }
]

発行されたSQLは以下のとおり

N+1問題を回避。 クエリを2回発行し、結果をマージして返している

2021-05-16 21:42:31.702 UTC [4365] LOG:  execute s0: SELECT "postgres"."Post"."id", "postgres"."Post"."title", "postgres"."Post"."content", "postgres"."Post"."published", "postgres"."Post"."authorId" FROM "postgres"."Post" WHERE "postgres"."Post"."title" LIKE $1 ORDER BY "postgres"."Post"."id" ASC LIMIT $2 OFFSET $3
2021-05-16 21:42:31.702 UTC [4365] DETAIL:  parameters: $1 = 'title%', $2 = '5', $3 = '0'
2021-05-16 21:42:31.706 UTC [4365] LOG:  execute s1: SELECT "postgres"."User"."id", "postgres"."User"."email", "postgres"."User"."name", "postgres"."User"."tel" FROM "postgres"."User" WHERE "postgres"."User"."id" IN ($1) OFFSET $2
2021-05-16 21:42:31.706 UTC [4365] DETAIL:  parameters: $1 = '1', $2 = '0'

Native Query ［パラメータ埋め込み］

Prismaでは、SQLベタ書きも利用できる。

import { User } from '@prisma/client' // 追加

const prisma = new PrismaClient()

async function main() {
    const email = 'alice@example.com'
    const alice = await prisma.$queryRaw<User>(`SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = '${email}';`)
    console.log(alice)
}
  • クエリ発行
% npx ts-node prisma/index.ts
[ { id: 1, email: 'alice@example.com', name: 'Alice', tel: null } ]

発行されたSQLは以下のとおり

2021-05-16 11:48:12.345 UTC [3158] LOG:  execute s0: SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = 'alice@example.com';

SQLインジェクション確認（Native Query ［パラメータ埋め込み］）

SQLインジェクションの可能性がSQLログから確認できたため、以下を試してみる

    const email = "'' OR 1=1 ORDER BY 1 DESC"
    const alice = await prisma.$queryRaw(`SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = ${email};`)
    console.log(alice)
  • クエリ発行
% npx ts-node prisma/index.ts
[
  { id: 2, email: 'bob@example.com', name: 'Bob', tel: null },
  { id: 1, email: 'alice@example.com', name: 'Alice', tel: null }
]

発行されたSQLは以下のとおり

2021-05-16 11:54:18.659 UTC [3173] LOG:  execute s0: SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = '' OR 1=1 ORDER BY 1 DESC;

この方式を検索フォームなどのユーザ入力を受け付ける箇所で利用するのは、危険なことが確認できた。

Native Query ［パラメータ指定］

同様のクエリーをパラメータ指定で試してみる。

    const alice = await prisma.$queryRaw(`SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = $1`, 'alice@example.com')
    console.log(alice)
  • クエリ発行
% npx ts-node prisma/index.ts
[ { id: 1, email: 'alice@example.com', name: 'Alice', tel: null } ]

発行されたSQLは以下のとおり

2021-05-16 12:02:11.912 UTC [3196] LOG:  execute s0: SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = $1
2021-05-16 12:02:11.912 UTC [3196] DETAIL:  parameters: $1 = 'alice@example.com'

プリペアドステートメント（Prepared statements）になってくれた。

SQLインジェクション確認（Native Query ［パラメータ指定］ ）

同様のパラメータを指定して、動作を確認してみる。

    const alice = await prisma.$queryRaw(`SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = $1`, "'' OR 1=1 ORDER BY 1 DESC")
    console.log(alice)
  • クエリ発行
% npx ts-node prisma/index.ts
[]

発行されたSQLは以下のとおり

2021-05-16 12:04:31.690 UTC [3202] LOG:  execute s0: SELECT * FROM "postgres"."User" WHERE "postgres"."User"."email" = $1
2021-05-16 12:04:31.690 UTC [3202] DETAIL:  parameters: $1 = ''''' OR 1=1 ORDER BY 1 DESC'

パラメータがエスケープされ、正しく解釈された。

結果、Native Queryは、パラメータ指定の方式なら、画面側でも用いることはできそう。