How to set dynamic params for SQL query in C#

Question

Hi i have to add dynamic params for {0}{1} but i dont know how that works. Can someone help me please and explain it to me?

 string strCmdExclChainLast = string.Format("SELECT SUM(F9H.PCPQ9H) AS ValueLast " +
                                                   "FROM {3}.F9H00 F9H " +
                                                   "WHERE F9H.PCPU9H = " +
                                                                       "(SELECT C02.YMDU02 " +
                                                                       "FROM {3}.C0200 C02 " +
                                                                       "WHERE C02.HTYC02 = '0' AND C02.YMDU02 < {0}{1}{2} " +
                                                                       "ORDER BY C02.YMDU02 DESC " +
                                                                       "FETCH FIRST ROW ONLY) " +
                                                   "AND F9H.LN1C9H NOT IN ('130', '211', '311', '360', '411', '530', '540')",
                                                   DateTime.Now.Year,
                                                   DateTime.Now.Month.ToString().Length == 1 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString(),
                                                   DateTime.Now.Day.ToString().Length == 1 ? "0" + DateTime.Now.Day.ToString() : DateTime.Now.Day.ToString(),
                                                   Program.Wavedlib);

        dtExclChain.Rows[0]["ValueLast"] = DBOps.ExecDataTableDB2AS400(strCmdExclChainLast).Rows[0]["ValueLast"];

Does this answer your question? [String.Format - how it works and how to implement custom formatstrings](https://stackoverflow.com/questions/10512349/string-format-how-it-works-and-how-to-implement-custom-formatstrings) — Sinatr, Apr 27 '21 at 07:19
What is your DBMS? What do you use to communicate with the database in C#? Mitch is right that we would usually have a database class that lets us pass the parameter as a date directly. This wouldn't work for the schema name, though. What data type is YMDU02? What does your query result in? Do you get an error from the DBMS? — Thorsten Kettner, Apr 27 '21 at 07:30
Why do you have to pass the date anyway? Doesn't your DBMS know what day is today? — Thorsten Kettner, Apr 27 '21 at 07:36
DBMS = DataBase Management System, meaning, what is the "database engine" you are using. It could be for instance: MySQL, Microsoft SQL Server, Oracle DB, Sqlite, PostgreSQL, ... Each of it use it's own "flavor" of the SQL language, and some internal subtleties, with some features that may or may not be available or advisable in others,. — Pac0, Apr 27 '21 at 07:46
You haven't written the code, you are new to C# and you don't know what a DBMS is, you don't know the data type and you don't know what happens when you run the code and whether the DBMS is issuing an error message? I don't want to sound harsh, but maybe someone else should care about this. Then learn and practise with C# and SQL separately, before trying to combine the two. — Thorsten Kettner, Apr 27 '21 at 07:52

score 1 · Answer 1 · answered Apr 27 '21 at 07:25

For this task you ca use Dapper (https://github.com/DapperLib/Dapper). This is very lightweight library. This lib is very similar to string.Format, but it works correct when you want to build SQL query.

As example it work like this.

public class Dog
{
    public int? Age { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
}

var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

Assert.Equal(1,dog.Count());
Assert.Null(dog.First().Age);
Assert.Equal(guid, dog.First().Id);

score 0 · Answer 2 · answered Apr 27 '21 at 07:44

If you put $ @ signs at the beginning of your SQL query, you can use dynamic parameters and special characters inside the query. Like this:

$@"SELECT SUM(F9H.PCPQ9H) AS ValueLast " +
                                               "FROM {3}.F9H00 F9H " +
                                               "WHERE F9H.PCPU9H = " +
                                                                   "(SELECT C02.YMDU02 " +
                                                                   "FROM {3}.C0200 C02 " +
                                                                   "WHERE C02.HTYC02 = '0' AND C02.YMDU02 < {0}{1}{2} " +
                                                                   "ORDER BY C02.YMDU02 DESC " +
                                                                   "FETCH FIRST ROW ONLY) " +
                                               "AND F9H.LN1C9H NOT IN ('130', '211', '311', '360', '411', '530', '540')"

We can do this, but we shouldn't choose it. Because this makes our queries vulnerable to SQL Inj attacks.

You can check out this article about sql injection https://www.netsparker.com/blog/web-security/sql-injection-cheat-sheet/ — atilaykosker, Apr 27 '21 at 07:46
Never use direct variables in sql queries. In many cases this should not bea problem, but in some cases I might be able to set your variable to "... DROP DATABASE;--". A few years ago I was able to read credit card information from community users with a "UNION SELECT" injecting to a website id parameter — Klamsi, Apr 27 '21 at 07:51
I think we have made clear how this can be done and why it should not be done. — atilaykosker, Apr 27 '21 at 07:56

How to set dynamic params for SQL query in C#

2 Answers2