Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Feature Request] Using InterpolatedStringHandler for templated command, becuase its pretty #2066

Open
LittleNetworkHack opened this issue Apr 3, 2024 · 0 comments

Comments

@LittleNetworkHack
Copy link

Idea is simple, but i know there will be some pushback from people who scream SQL injection.

Im wrote my own micro ORM, there is outdated open source version here (this is link to relevant file): https://github.com/LittleNetworkHack/Crone/blob/main/Crone.Core/Components/Data/CoreDataCommandBuilder.cs

I use string builder for command text and dictionary to store parameters before converting it into ADO.NET provider specific command.
The way i use it is following:

public static void TestCommand(string ownerName, string viewName)
{
	CoreDataCommandBuilder command =
	$"""
	SELECT *
	FROM   ALL_VIEWS
	WHERE  OWNER = {ownerName}
	AND    VIEW_NAME = {viewName}
	""";

	var result = _provider.ExecuteDataTable(command);
}

This transalted into command text with placeholder and optionally (if bind by name) with name:

SELECT *
FROM   ALL_VIEWS
WHERE  OWNER = ~@ownerName
AND    VIEW_NAME = ~@viewName

Then ~@ is replace with provider specific binder like : for oracle.
Command from dictionary are added as parameters.

Pros:
Proper multiline command support
IDE highlight where parameters are actually places not just big orange text

Cons:
Idk, you tell me

Iterpolated string builder supports overloading for AppendFormatted types like integer, decimal, datetime...
I even handle int[] or string[] for SELECT * FROM TABLE WHERE ID IN ({array_parameter}) so i convert them into CSV value added directly into command text, this can be further sanitized if needed.

This is just idea, let me know what you think about it, i can go into more details about implementation if needed. Like support for formatting {DateTime.Now:YYYY-MM-DD} and RAW for actual SQL injection.

NOTE: ive built this before i saw EF Core doing something similar with execute raw sql, since i always wanted to write commands in a way that felt natural to me.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant