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

1
0
4/7/2019 11:36:19 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

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.

1
4/7/2019 2:39:50 PM

Related Questions

Raw SQL queries and Entity Framework Core

Make sure you add ...using Microsoft.Data.Entity;... because there is an extension method you could use....var rawSQL = dbContext.SomeModels.FromSql("your SQL"); ...Even better, instead using raw SQL (at risk of SQL injections attacks) this FromSql method allows you to use parameterized queries like:...dbContext.SomeModels.FromSql("SELECT * FROM db...
asp.net-core asp.net-core-mvc c# entity-framework-core
asked by diegosasw

Raw SQL Query without DbSet - Entity Framework Core

Building on the other answers I've written this helper that accomplishes the task, including example usage:...public static class Helper { public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map) { using (var context = new DbContext()) { using (var command = context.Database.GetDbConnection()...
c# entity-framework-core
asked by pius

Raw SQL Query without DbSet - Entity Framework Core

Building on the other answers I've written this helper that accomplishes the task, including example usage:...public static class Helper { public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map) { using (var context = new DbContext()) { using (var command = context.Database.GetDbConnection()...
c# entity-framework-core
asked by pius

Entity Framework Core - Execute raw sql - Same code for SQL/SQLite

It's maybe not exactly, what you want , but you can avoid doubling code by using DbConnection instead of SQLConnection. You can define the DbConnectionType anywhere else like:...Type connectionType = _hostingEnvironment.EnvironmentName == "Test" ? typeof(SqliteConnection) : typeof(SqlConnection); // If you switch the Connection String outside too,...
.net c# entity-framework-core sql sqlite
asked by Nikolaus

Entity Framework 6 vs Entity Framework Core Raw Sql

I have done the 'fromsql' off of the context directly when it is a single table, but I realize this is not what you want but it builds on it....var blogs = context.Blogs .FromSql("SELECT * FROM dbo.Blogs") .ToList(); ...However in a case like yours it is complex and a joining of multiple tables and CTEs. I would suggest you create a custom...
entity-framework-6 entity-framework-core
asked by djangojazz

Unable to get complete SQL text from Entity Framework Core queries containing Union

Are you sure that you get tbe complete SQL with the extension method ToSql? This line:... var sql = modelVisitor.Queries.First().ToString(); ...Can a Union have two queries?...Try first to loop through queries and log them to the console forexample....foreach (var query in modelVisitor.Queries) Console.WriteLine(query); ...That will call toStrin...
.net-core c# entity-framework-core iqueryable
asked by Tore Aurstad

Raw SQL Query without DbSet - Entity Framework Core

Building on the other answers I've written this helper that accomplishes the task, including example usage:...public static class Helper { public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map) { using (var context = new DbContext()) { using (var command = context.Database.GetDbConnection()...
c# entity-framework-core
asked by pius

How to execute RAW SQL Query with EF Core?

It worked like this:...private void SqlCommand (string connectionString, string query) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.Ex...
asp.net-core c# entity-framework-core
asked by Daniel Acevedo

Why Entity Framework core does not support raw sql query?

You can use the extension method here to create an ad-hoc DbContext for your type: ...https://github.com/aspnet/EntityFrameworkCore/issues/1862#issuecomment-451671168
entity-framework entity-framework-core
asked by ErikEJ

Executing Raw sql with Entity Framework Core

ADO.NET works in EFCore =)...using Microsoft.EntityFrameworkCore; using System.Data.Common; using System.Data.SqlClient; using System; public void ExampleMethod(DbContext context) { SomeObjectResult result = null; DbCommand cmd = context.Database.GetDbConnection().CreateCommand(); cmd.CommandText = "Select C.ID, C.C...
asp.net-core entity-framework entity-framework-core
asked by DespeiL

One LINQ query to get counts from several entities when using entity framework core

That's a one LINQ query solution, i hope this will be helpful,...var result = Context.UserAccounts.Select(x => new List<int>() { 1, 0, 0, 0 }).Union(Context.GuestAccounts.Select(y => new List<int>() { 0, 1, 0, 0 })) .Union(Context.WorkGroups.Where(x => x.IsActive).Select(z => new List<int>() { 0, 0, 1, 0 })).Union(Context.WorkItems.Sele...
.net-core c# entity-framework-core linq
asked by Furkan Öztürk

How to retrieve an entity with related entities in .net core with raw SQL queries?

The ...documentation... for ....FromSqlRaw... mentions this in the ...limitations section...They do give an example of how to ...include related data... however by composing over the raw query:...var visits = dbContext.Visits .FromSqlRaw("select * from visit") .Include(v=>v.prescriptions) .ToList(); ...I would also strongly recommend that you...
asp.net-core c# entity-framework-core linq sql
asked by Robert McKee

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow