How to generate a SQL Command string that is safe from SQL Injection?-CodePudding

I have a situation where I have to take input from a user, create a SQL command and send that command to a service that will execute the SQL. The service ONLY allows for a SQL string -- not additional parameters; so I am forced to create the entire SQL statement on my end of things. I do not have any kind of access to the database itself -- only a service that sits overtop of it.

I realize the following is NOT safe:

var sql = $"SELECT * FROM tablename WHERE name = '{incomingdata.searchName}'";

But if I generate SQL with parameters, would this be safe from SQL injection?

var sql = $@"
DECLARE @Name varchar(50);
SET @Name = '{incomingdata.searchName}';
SELECT * FROM tablename WHERE name = @Name";

CodePudding user response：

Disclosure: My background is C , Java and TypeScript/JavaScript, not C#

Would this be more appropriate:

SqlCommand sql = new SqlCommand("SELECT * FROM tablename WHERE name = @Name");
sql.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = incomingdata.searchName);

Sanitizing the user data before it gets to this stage, using a trusted package, might also be helpful.

CodePudding user response：

This is not an ideal situation. I would try and look for a parametrized way to solve this problem failing that I would test the input and in ANY case where a test fails not allow the query at all and ask the user to re-enter.

Do the following tests:

Length of input is smaller than a max name size (25 characters?)
All input characters are in the alphabet
No reserved SQL words (easy to find with a google search)

If the input does not fail any of these tests you should be OK. DON'T try to sanitize the input -- this can be hard/impossible to do with international character sets.

CodePudding user response：

You are along the right lines. You never want to use a variable that can be changed. Instead, you need to use SQL parameters. You can add a SQL parameter like this:

command.Parameters.Add(new SqlParameter("@Name", "incomingdata.searchName"));

And then refer to it in your query like this:

SELECT * 
FROM tablename 
WHERE name = @Name";

Once this is done, when a user tries to change the value of a variable the query will return no results. It should be said that this way of doing it does result in the SQL property assuming the type of the C# variable. There are other ways of doing this if you want to specify the type of the property to be different from the variables type. This is a useful resource https://jonathancrozier.com/blog/preventing-sql-injection-in-c-sharp-applications