How to execute a raw union SQL query on several tables with Entity Framework Core

Question

How to execute a raw union SQL query on several tables with Entity Framework Core

ajax c# entity-framework-core sqlite

Question

I'm programming a web application with intentionally implemented vulnerabilities for a CTF competition and I'm using ASP.NET Core and Entity Framework Core with a SQLite database. I want to make a SQL injection possible and in order to that, I need to somehow query the database on several tables with an union statement.

Unfortunately all my attempts resulted in error messages. The backend controller takes an argument via an ajax request and the client should misuse the UserId variable and modify the parameters in the ajax request to get more information about another table.

I tried several FromSql approaches but none of them worked out. Does someone know how I can solve this?

This is the backend controller code

public JsonResult ListImagePaths(string UserId)
{
    string query = $"SELECT * FROM UserImage WHERE UID == {UserId}";

    var results = mContext.UserImage.FromSql(query);           

    return Json(results);
}

This way I only do a query on the UserImage table. I did not find a way to query two tables. I tried to create a DbSet that consists of attributes in both tables but this didn't work too.

This is the client side ajax request

$(document).ready(function () {
    $.ajax({
        type: 'GET',
        url: 'ListImagePaths/"27b988e3-77ab-41e4-b945-6ac9315a70ec"',
        success: function (result){
            console.log(result)
        }
    })
})

Here is a sample of the UserImage table

Uid = 27b988e3-77ab-41e4-b945-6ac9315a70ec
Path = CloudImages/picture.jpg
Id = e802a368-229f-4ed5-ba84-42ce4da5ed0a

Here is a sample of the AspNetUsers table

Id = 81858e0d-e75f-45c5-8054-ef956e843a04
UserName = SomeGuy43
NormalizedUserName = SOMEGUY43
Email = myemail@somehere.com
NormalizedEmail = MYEMAIL@SOMEWHERE.COM
EmailConfirmed = 1
PasswordHash = uRGWeGWq5ZorrzgNvSuz8Q==
SecuritySamp = RBCZDCBA3IEMPET2ZCL4MVCXW3IA3H4C
ConcurrencyStamp = 0b8873cc-ecfe-4280-a6bf-0f354242f4a4
PhoneNumber = NULL
PhoneNumberConfirmed= 0
TwoFactorEnabled = 0
LockoutEnd = 0
LockoutEnabled = 0
AccessFailedCount = 0
AvatarImage = pictures/avatar.jpg
Salt = 4r1rtuji0cc
IsAdmin = 0

Coziac

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 4/7/2019 2:39:50 PM

I did not find a way to query two tables. I tried to create a DbSet that consists of attributes in both tables but this didn't work too.

Not sure what exactly "didn't work", but this is the way to go. But instead of creating DbSet of the class containing the common attributes, thus telling EF to treat is an entity, create DbQuery or simply register that class as query type.

For instance:

public class MyQueryType
{
    public Guid Id { get; set; }
    publc string Text { get; set; }
}

Inside your DbContext class, either:

public DbQuery<MyQueryType> MyQueryType { get; set; }

or (inside OnModelCreating override):

modelBuilder.Query<MyQueryType>();

Both will allow you to use MyQueryType as target for FromSql:

string query = $"SELECT Id, Path AS Text FROM UserImage WHERE UID == {UserId}"
    + $" UNION ALL SELECT Id, UserName AS Text FROM AspNetUsers";

var results = mContext.Query<MyQueryType>().FromSql(query);

Just make sure the SQL query returns the expected column names and types by using aliases as shown above.

How to execute a raw union SQL query on several tables with Entity Framework Core

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related