C# SQL pass variable to query

Question

I am currently writing a Search function that bring down a value Name

Here is my query:

"SELECT Company.Name, Company.Reg FROM Company WHERE Name LIKE '%''" + Name + "''%'";

Here is the function:

public object CompanySearch(string Name)
        {
            using (PCE)
            {
                SqlConnection con = new SqlConnection(constr);
                try
                {
                    List<CompanySearch> cm = new List<CompanySearch>();
                    SqlCommand command = new SqlCommand();
                    command.Connection = con;
                    "SELECT Company.Name, Company.Reg FROM Company WHERE Name LIKE '%''" + Name + "''%'";
                    con.Open();
                    //process the sql execute etc
                }
            }
        }

Is the way I reading Name correctly?

I tested without ' ' , however I get an exception message as follow:

"ExceptionMessage": "Incorrect syntax near 'Mysearch'.",

UPDATE

SELECT Company.Name, Company.Reg
FROM Company
WHERE CompanyName LIKE '%MySearch%';

This is the code that I execute in SSMS, and it went sucess. However it doesnt work on my C#

Your query is missing a FROM, and you got too many single quotes.. And, perhaps most importantly, you should parameterize your query. — HoneyBadger
– HoneyBadger, Commented May 14, 2020 at 7:21
Once you've got it working, try replacing Mysearch with Mysearch' ; DROP TABLE Company; -- (in the UI). By the way, you should back up your database before attempting this. Once you've realised the damage SQL injection can do, see HoneyBadger's suggestion of parameterising your query. — ProgrammingLlama
– ProgrammingLlama, Commented May 14, 2020 at 7:24
Oh, and you don't assign your query, it's just an unrelated, solitary string which should give an error long before you come close to executing this, — HoneyBadger
– HoneyBadger, Commented May 14, 2020 at 7:25
You should read up on SQL Injection and why you shouldn't do what you're doing. Try and make sure you use parameters instead (in this case a param for 'Name') — sr28
– sr28, Commented May 14, 2020 at 7:26
@Lawraoke Yes, but if the user inputs MySearch' ; DROP TABLE Company; -- then the final query will be: SELECT Company.Name, Company.Reg FROM Company WHERE CompanyName LIKE '%MySearch' ; DROP TABLE Company; -- %';: Two SQL commands (a select and a drop table) and then a comment. This is why you should use parameters instead. — ProgrammingLlama
– ProgrammingLlama, Commented May 14, 2020 at 7:56

Pitming Sir · Accepted Answer · 2020-09-09 07:42:36Z

3

First of all, you should always avoid to "manually build" your own query. This is the best way to have SQL Injection (https://en.wikipedia.org/wiki/SQL_injection)

Secondary, you should used Parameter in your query

SqlCommand cmd = new SqlCommand("SELECT Company.Name, Company.Reg WHERE Name LIKE @companyName", connection);
cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = Name;

But in 2020, you should use an ORM instead of building your own query. This is far better to save time and avoid bugs. Have a look at EF Core (https://learn.microsoft.com/fr-fr/ef/) or Dapper (https://stackexchange.github.io/Dapper/), ...

edited Sep 9, 2020 at 7:42

answered May 14, 2020 at 7:31

Pitming Sir

874 bronze badges

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

1 Comment

Lawraoke Over a year ago

Hi @Pritming Sir, thanks for your answer, do you have more example on your parameters?

StackNeverFlow · Accepted Answer · 2020-06-04 01:33:32Z

1

This is what I try, it worked for me...

 public object CompanySearch(string Name)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {
                List<CompanySearch> cs = new List<CompanySearch>();
                SqlCommand command = new SqlCommand();
                command.Connection = con;
                command.CommandText = "SELECT Name, Reg, FROM Company WHERE Name LIKE '%" + Name + "%'";
                con.Open();
                //process the sql execute etc
            }
        }
    }

However, a good practice is to parameterize your query that mentioned by @HoneyBadger

answered Jun 4, 2020 at 1:33

StackNeverFlow

10511 bronze badges

Collectives™ on Stack Overflow

C# SQL pass variable to query

UPDATE

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related