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

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 = [email protected]
NormalizedEmail = [email protected]
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

I believe you can put .Include before the FromSql to include related tables? — NetMage
I'm experimenting with .Include now. var results = mContext.UserImage.Include( u => u.Users)FromSql(query); But i can only access attributes of the UserImage table. @mason The application should allow SQL-Incjections — Coziac
Please provide an example of the tables you want to UNION. e.g. at least two sample entity classes representing tables. — Ivan Stoev
But these have nothing in common - they are more like candidates for JOIN rather than UNION. I am expecting to see the classes you used in I tried to create a DbSet that consists of attributes in both tables, i.e. the two entity classes and the class with the common attributes. — Ivan Stoev

Ivan Stoev Ivan Stoev · Accepted Answer · 2019-04-07T14:34:20

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

2 Answers