How to use string variable in sql statement

Question

I have a WPF Application in which I am getting

string someone = TextBox.text;

I would like to use this in the following query

query = " Select * From Table Where Title = someone "

How should I go about using the variable someone in the query?

you shouldnt! Ever hear of SQL Injection or parameterized queries? — Dustin Davis
– Dustin Davis, Commented Aug 31, 2011 at 21:52
This question is too broad, it amounts to how do i get data from sql server based on user input in a WPF application. There are entire books just covering that. — Coding Flow
– Coding Flow, Commented Aug 31, 2011 at 21:54
here you can read some SQL Injection examples en.wikipedia.org/wiki/SQL_injection — Cacho Santa
– Cacho Santa, Commented Aug 31, 2011 at 21:54

msarchet · Accepted Answer · 2011-09-01 14:11:46Z

27

You can just do this

query = "Select * From Table Where Title = " + someone;

But that is bad and opens you to SQL Injection

You should just use a parameterized query

Something like this should get you started

using (var cn = new SqlClient.SqlConnection(yourConnectionString))
using (var cmd = new SqlClient.SqlCommand())
{
   cn.Open();
   cmd.Connection = cn;
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = "Select * From Table Where Title = @Title";
   cmd.Parameters.Add("@Title", someone);
}

From Jon Skeet's answer since his was more complete than mine

See the docs for SqlCommand.Parameters for more information.

Basically you shouldn't embed your values within the SQL itself for various reasons:

It's inelegant to mix code and data
It opens you up to SQL injection attacks unless you're very careful about escaping
You have to worry about formatting and i18n details for things like numbers, dates and times etc
When the query remains the same with only the values changing, the optimizer has less work to do - it can look up the previous optimized query directly as it'll be a perfect match in terms of the SQL.

edited Sep 1, 2011 at 14:11

answered Aug 31, 2011 at 21:56

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

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

2 Comments

Si8 Over a year ago

What if I have multiple variable I have to add?

msarchet Over a year ago

Use multiple parameters. Would look something like cmd.CommandText = "Select * From Table Where Tile = @Title, Thing = @Thing cmd.Parameters.Add("@Title", someone); cmd.Parameters.Add("@Thing, thing);

Jon Skeet · Accepted Answer · 2017-02-11 08:08:55Z

14

You should use a parameterized SQL query:

query = "SELECT * From TableName WHERE Title = @Title";

command.Parameters.Add("@Title", SqlDbType.VarChar).Value = someone;

See the docs for SqlCommand.Parameters for more information.

Basically you shouldn't embed your values within the SQL itself for various reasons:

It's inelegant to mix code and data
It opens you up to SQL injection attacks unless you're very careful about escaping
You have to worry about formatting and i18n details for things like numbers, dates and times etc
When the query remains the same with only the values changing, the optimizer has less work to do - it can look up the previous optimized query directly as it'll be a perfect match in terms of the SQL.

edited Feb 11, 2017 at 8:08

answered Aug 31, 2011 at 21:53

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

1 Comment

msarchet Over a year ago

I pulled in the meat of your answer to mine since mine got accepted so at least someone looking at the accepted answer will see the fullest of information.

Community · Accepted Answer · 2017-05-23 12:17:00Z

1

Easiest is to use a C# Prepared sql. Example on this post. You don't have to worry about escaping the characters in your sql string or anything

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Aug 31, 2011 at 21:54

Ali

12.7k10 gold badges57 silver badges83 bronze badges

Comments

Pang · Accepted Answer · 2018-03-06 06:42:19Z

0

declare @SqlQuery varchar(2000), @Fromdate varchar(20), @Todate varchar(20)

set @Fromdate='01 jan 2017'
set @Todate='30 mar 2017'


set @SqlQuery='select * from tblEmployee where tblEmployee.JDate between '''+ @Fromdate + ''' and '''+ @Todate+ ''''

print  @SqlQuery

edited Mar 6, 2018 at 6:42

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Mar 6, 2018 at 6:21

Ashim

11 bronze badge

Collectives™ on Stack Overflow

How to use string variable in sql statement

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related