はじめに
複雑な SQL を発行する場合、LINQ to Entities で頑張るよりも、
生の SQL をそのまま書きたい場合があります。
EF Core で生SQLクエリを流すにはFromSql拡張メソッドを使用します。
var posts = context.Posts
.FromSql("select * from Posts") //=> `FromSql`での生クエリは、モデルのすべてのプロパティを返す必要があるためカラムを全て取得
.ToList();
生のクエリを流す場合は SQL インジェクションの対策が必須です。
実際に環境を構築してSQL インジェクションを発生させ、その対策をしてみます。
環境構築
# プロジェクトの作成
PS> dotnet new console -o RawQuery
# プロジェクトディレクトリに移動します
PS> cd ./RawQuery
# 今回データベースには SQL Server Local DB を使います
PS> dotnet add package Microsoft.EntityFrameworkCore.SqlServer
# SQL インジェクションの際、どのようなSQLが流れているか確認するため、ロガーを使います
PS> dotnet add package Microsoft.Extensions.Logging.Console
# dotnet コマンドでマイグレーションを行うため必要です
PS> dotnet add package Microsoft.EntityFrameworkCore.Design
モデルはブログの投稿とします。
class Post {
public int ID { get; set; }
public string Title { get; set; }
public bool IsPrivate { get; set; }
public string Category { get; set; }
}
データベースコンテキストでロガーや DB 接続を設定します。
今回は最初からシードデータを持たせておきます。
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
class BlogContext : DbContext {
public DbSet<Post> Posts { get; set; }
static readonly ILoggerFactory loggerFactory = new LoggerFactory(new[] {
new ConsoleLoggerProvider((category, level) =>
category == DbLoggerCategory.Database.Command.Name
&& level == LogLevel.Information, includeScopes: true) });
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
optionsBuilder
.EnableSensitiveDataLogging()
.UseLoggerFactory(loggerFactory)
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=MyDB;Trusted_Connection=True;");
protected override void OnModelCreating(ModelBuilder modelBuilder) =>
modelBuilder.Entity<Post>().HasData(
new Post {
ID = 1,
Title = "C# 8 ロードマップ",
Category = "C#",
IsPrivate = false
},
new Post {
ID = 2,
Title = "Array.some と Array.includes の使い分け",
Category = "JavaScript",
IsPrivate = false
},
new Post {
ID = 3,
Title = "秘密のポートフォリオ",
Category = "C#",
IsPrivate = true
});
}
以下のコマンドでマイグレーションファイルをスキャフォールディングし、データベースを生成します。
PS> dotnet ef migrations add Initial
PS> dotnet ef database update
典型的な SQL インジェクション
ブログサービスで公開されている投稿を一覧を取得する処理があったとします。
以下の例では、公開されている記事をカテゴリで検索する SQL を文字列結合で組み立てています。
class Program {
static void Main(string[] args) {
using (var context = new BlogContext()) {
string category = "C#"; //=> 検索条件として渡されたと仮定
var posts = context.Posts
.FromSql("select * from Posts where Category = '" + category + "' and IsPrivate = 0")
.ToList();
foreach (var p in posts) {
Console.WriteLine($@"
{p.Title}
{p.Category}
{(p.IsPrivate ? "非公開" : "公開")}");
}
}
}
}
dotnet runすると以下のログが流れます。
条件通り、公開記事でカテゴリが C# のレコードが取得できました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (40ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
select * from Posts where Category = 'C#' and IsPrivate = 0
タイトル: C# 8 ロードマップ
カテゴリ: C#
公開: Ｏ
それでは SQL インジェクションを実際に起こしてみます。
検索条件として1' or 1 = 1 --を渡します。
string category = "1' or 1 = 1 -- ";
var posts = context.Posts
.FromSql("select * from Posts where Category = '" + category + "' and IsPrivate = 0")
.ToList();
dotnet runで動かしてみると非公開記事まで取得できてしまいました。／(^o^)＼
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
select * from Posts where Category = '1' or 1 = 1 -- ' and IsPrivate = 0
タイトル: C# 8 ロードマップ
カテゴリ: C#
公開: Ｏ
タイトル: Array.some と Array.includes の使い分け
カテゴリ: JavaScript
公開: Ｏ
タイトル: 秘密のポートフォリオ
カテゴリ: C#
公開: Ｘ
どうして意図しないレコードが取得できてしまったのか？
1' or 1 = 1 --が検索条件として渡された場合、ログを確認すると以下の SQL が発行されていました。
select * from Posts where Category = '1' or 1 = 1 -- ' and IsPrivate = 0
まず、1 = 1は常に真となります。
また、-- 以降のand IsPrivate = 0はコメントとして無視されます。
1 = 1は'or'で指定されているため、Category = '1'は無視されます。
結果、テーブルのすべてのレコードが取得できてしまいます。
生 SQL を発行する際の SQL インジェクション対策
SQLにパラメータを渡すときは文字列結合を使ってはいけません。
今まで触れませんでしたが、今どきの Visual Studio は賢いので警告を出しています。
ログにも警告が出ています。
一般的に、シングルクォートなどの特定の文字列をエスケープする対策が知られていますが、EF Core には対策が用意されています。
DbParameter・プレースホルダ・文字列補完式を使うと、パラメータ化クエリに置き換えてくれます。
DbParameter
var category = new SqlParameter("category", "C#");
var posts = context.Posts
.FromSql("select * from Posts where Category = @category and IsPrivate = 0", category)
.ToList();
- プレースホルダ
var category = "C#";
var posts = context.Posts
.FromSql("select * from Posts where Category = {0} and IsPrivate = 0", category)
.ToList();
- 文字列補間式
var category = "C#";
var posts = context.Posts
.FromSql($"select * from Posts where Category = {category} and IsPrivate = 0")
.ToList();
直感的で、IDE の支援も受けやすい文字列補間式がよさそうです。
文字列補間式でなぜSQL インジェクションが防げるかちょっと不思議です。
文字列結合した場合と同じ SQL が発行される気がします。
Stack Over Flow でも同じ疑問を持った方がいたようで、回答を読むとFromSqlの引数は文字列でなくFormattableStringで、SqlParameterに置き換えられるそうです。
Stack over flow の質問と回答
RelationalQueryableExtensions.FromSql
SQL インジェクションを起こすコードを文字列補完式で置き換えてdotnet runしてみます。
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (65ms) [Parameters=[@p0='1' or 1 = 1 -- ' (Size = 4000)], CommandType='Text', CommandTimeout='30']
select * from Posts where Category = @p0 and IsPrivate = 0
SQL を見るとのパラメータに置き換えられています。
select * from Posts where Category = @p0 and IsPrivate = 0
不正なクエリは発行されなくなりました。
今回の場合、条件に一致するレコードはないので一軒も取得されません。
検索条件のカテゴリにC#を指定すると正常に取得できます。
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (71ms) [Parameters=[@p0='C#' (Size = 4000)], CommandType='Text', CommandTimeout='30']
select * from Posts where Category = @p0 and IsPrivate = 0
タイトル: C# 8 ロードマップ
カテゴリ: C#
公開: Ｏ
この記事のコードは以下のリポジトリに置いてあります。
https://github.com/sano-suguru/RawQuery
スキルアップを目指して毎日猛勉強中です。
エンジニアやエンジニア志望の方と繋がりたいです( ´∀`)
のさ@nosa_programmer