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 — Sinatr
– Sinatr, Commented Apr 27, 2021 at 7: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
– Thorsten Kettner, Commented Apr 27, 2021 at 7:30
Why do you have to pass the date anyway? Doesn't your DBMS know what day is today? — Thorsten Kettner
– Thorsten Kettner, Commented Apr 27, 2021 at 7: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
– Pac0, Commented Apr 27, 2021 at 7:46

TemaTre · Accepted Answer · 2021-04-27 07:25:26Z

1

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);

answered Apr 27, 2021 at 7:25

TemaTre

1,4442 gold badges13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

atilaykosker · Accepted Answer · 2021-04-27 07:44:47Z

0

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.

answered Apr 27, 2021 at 7:44

atilaykosker

943 bronze badges

3 Comments

atilaykosker Over a year ago

You can check out this article about sql injection netsparker.com/blog/web-security/sql-injection-cheat-sheet

Klamsi Over a year ago

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

atilaykosker Over a year ago

I think we have made clear how this can be done and why it should not be done.

Collectives™ on Stack Overflow

How to set dynamic params for SQL query in C#

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related