ScalikeJDBC と SQL インジェクション

SQLInterpolation

ScalikeJDBC の SQLInterpolation では以下のような形でクエリを書きます。

val input = "Chris"

val (u, c) = (User.syntax("u"), Company.syntax("c"))
val foundUser: Option[User] = sql"""
  select 
    ${u.result.*}, ${c.result.*} 
  from 
    ${User as u} left join ${Company as c} on ${u.companyId} = ${c.id}
  where 
    ${u.name} = ${input}
""".map(User(u.resultName, c.resultName)).single.apply()

String Interpolation に Type Dynamic によるフィールドを色々と埋め込みますが、マクロによるコンパイルチェックがあるので、こう見えて実は意外とタイプセーフです（「select」「left join」のように直接書いている部分は対象外です）。

このコードは実際には以下のような SQL を実行します。

select 
  u.id as i_on_u, 
  u.name as n_on_u, 
  u.company_id as ci_on_u, 
  c.id as i_on_c, 
  c.name as n_on_c 
from 
  users u left join companies c on u.company_id = c.id
where 
  u.name = ?

ルールはシンプルで SQLSyntax という型の場合にだけ、プレースホルダにならずそのまま組み込まれます。その他の型は全てプレースホルダになります。上記の例では「${input}」以外は全てそのまま展開されているということになります。

危なくないの？

このように SQL 文字列に値を埋め込むとなると SQL インジェクション脆弱性について心配になる方も多いかと思います。

このエントリでは「その心配はありません」ということを説明したいと思います。

sandbox

すぐに試せるサンドボックスプロジェクトを用意しました。

build.sbt

scalaVersion := "2.10.1"

libraryDependencies ++= Seq(
  "com.github.seratch" %% "scalikejdbc" % "1.5.3",
  "com.github.seratch" %% "scalikejdbc-interpolation" % "1.5.3",
  "org.slf4j" % "slf4j-simple" % "[1.7,)",
  "org.hsqldb" % "hsqldb" % "[2,)"
)

sbt console

事前に以下を実行します。

import scalikejdbc._
import scalikejdbc.SQLInterpolation._

Class.forName("org.hsqldb.jdbc.JDBCDriver")
ConnectionPool.singleton("jdbc:hsqldb:file:db/test", "", "")

GlobalSettings.loggingSQLAndTime = LoggingSQLAndTimeSettings(
  enabled = true, logLevel = 'info
)

// prepare DB
DB autoCommit { implicit s =>
  try {
    // create tables
    sql"create table users(id bigint primary key not null, name varchar(255), company_id bigint)".execute.apply()
    sql"create table companies(id bigint primary key not null, name varchar(255))".execute.apply()
    // insert data
    sql"insert into users values (${1}, ${"Alice"}, ${None})".update.apply()
    sql"insert into users values (${2}, ${"Bob"}, ${1})".update.apply()
    sql"insert into users values (${3}, ${"Chris"}, ${1})".update.apply()
    sql"insert into companies values (${1}, ${"Typesafe"})".update.apply()
  } catch { case e: Exception => println(e.getMessage) }
}

// companies
case class Company(id: Long, name: Option[String])
object Company extends SQLSyntaxSupport[Company] {
  override val tableName = "companies"
  def apply(c: ResultName[Company])(rs: WrappedResultSet) = new Company(rs.long(c.id), rs.stringOpt(c.name))
}

// users
case class User(id: Long, val name: Option[String], companyId: Option[Long] = None, company: Option[Company] = None)
object User extends SQLSyntaxSupport[User] {
  override val tableName = "users"
  def apply(u: ResultName[User])(rs: WrappedResultSet): User = new User(rs.long(u.id), rs.stringOpt(u.name), rs.longOpt(u.companyId))
  def apply(u: ResultName[User], c: ResultName[Company])(rs: WrappedResultSet): User = {
    apply(u)(rs).copy(company = rs.longOpt(c.id).map(id => Company(c)(rs)))
  }
}

検証例

普通に渡す

まず普通に SQLInterpolation に値を渡した場合。当然、安全です。

implicit val session = AutoSession

val input = "'Chris' or id is not null"

val u = User.syntax("u")
val foundUser = sql"""
  select ${u.result.*} from ${User as u} where ${u.name} = ${input}
""".map(User(u.resultName)).single.apply()

実行される SQL は以下です。 「${input}」はプレースホルダになります。

select u.id as i_on_u, u.name as n_on_u, u.company_id as ci_on_u from users u 
  where u.name = ?

sqls で作ったものを渡す

sqls"" という String Interpolation を使って SQLSyntax をつくることができます。例えば以下のように where 句を作っておいて、それを渡すなどの用途が考えられます。

val input = "'Chris' or id is not null"

val u = User.syntax("u")
val where = sqls"where ${u.name} = ${input}"
val foundUser = sql"""
  select ${u.result.*} from ${User as u} ${where}
""".map(User(u.resultName)).single.apply()

この場合も sqls の中に埋め込んだ外部入力の値は全てプレースホルダになるので安全です。

select u.id as i_on_u, u.name as n_on_u, u.company_id as ci_on_u from users u 
  where u.name = ?

SQLSyntax のインスタンス作成に直接、外部入力を渡す

SQLSyntax.apply(String) の呼び出し、唯一、これだけが危険です（でした）。

val input = "'Chris' or id is not null"

val u = User.syntax("u")
val name = SQLSyntax(input)
val foundUser = sql"""
  select ${u.result.*} from ${User as u} where ${u.name} = ${name}
""".map(User(u.resultName)).single.apply()
// 全件取得になる、ここで TooManyRowsException が throw される

実行される SQL は以下です。プレースホルダは一切使われません。

select u.id as i_on_u, u.name as n_on_u, u.company_id as ci_on_u from users u 
  where u.name = 'Chris' or id is not null

しかし、1.5.3 からこの API の呼び出しが ScalikeJDBC 内部のみに限定され、利用側でこのようなコードを書くことはできなくなりました。

scala> val name = SQLSyntax(input)
<console>:15: error: method apply in object SQLSyntax cannot be accessed in object scalikejdbc.interpolation.SQLSyntax
       val name = SQLSyntax(input)
                  ^

1.5.2 までのコードで SQLSyntax.apply を呼んでいる箇所があれば、以下のように sqls"" を使ったコードに書き換えてください。

val input = "'Chris' or id is not null"
//val name = SQLSyntax(input)
val name = sqls"${input}"

SQLInterpolation が存在しなかった 2.9 までの標準である SQL(String) を文字列連結で組み立てるのは一般的な危険性が伴います。外部入力の扱いは十分注意して実装するようにしてください。

sql、sqls だけを使っていれば安全

ということで sql"..."、sqls"..." による string interpolation だけを使っていれば、SQL インジェクションの危険はありません。利便性だけでなく、安全性も高まるのでぜひ SQLInterpolation をお使いください。