SQL syntax...explain an easy query

TowerMan

Hi,

I known the theory behind behind using sql, but a bit crap with tHe syntax. I am using visual studio and c#. I have a bit of code that I got off a guy at work ( I would ask him this question but he is off now till after crimbo). Would someone be able to explain what is going on to me?

The code is

SqlDataSource1.SelectCommand = "SELECT * FROM Users WHERE UserFullname LIKE '%" + TextBox1.Text + "%'";

I know that it will return all user that are like TextBox1.Text. But what I am shady about is '%" + TextBox1.Text + "%'" how you declare a variable in a Sql statement?

Also I have try modding the command to
SqlDataSource1.SelectCommand = "SELECT * FROM Users WHERE UserFullname = "Peter";

But this is also does not work. I think the theory is right but my syntax is all wrong. How would I select all usernames that are called Peter?

Sorry for the rant!!

madramor

the sql is different for different DBs below should work
SELECT * FROM Users WHERE UserFullname = 'Peter';

like '%Peter%'
would select anything that contains Peter
ie (JohnPeter, sdjhdPeterasdksd)

Zaph0d

TowerMan wrote:

Hi,

I known the theory behind behind using sql, but a bit crap with tHe syntax. I am using visual studio and c#. I have a bit of code that I got off a guy at work ( I would ask him this question but he is off now till after crimbo). Would someone be able to explain what is going on to me?

The code is

SqlDataSource1.SelectCommand = "SELECT * FROM Users WHERE UserFullname LIKE '%" + TextBox1.Text + "%'";

I know that it will return all user that are like TextBox1.Text. But what I am shady about is '%" + TextBox1.Text + "%'" how you declare a variable in a Sql statement?

In this case, the variable is declared by your client-side code and then used to construct a SQL statement with a constant in the match expression.

Also I have try modding the command to
SqlDataSource1.SelectCommand = "SELECT * FROM Users WHERE UserFullname = "Peter";

SQL uses single quotes around strings. This line of code has three double quotes so it was never going to work.

bonkey

TowerMan wrote:

I have a bit of code that I got off a guy at work

...

The code is

SqlDataSource1.SelectCommand = "SELECT * FROM Users WHERE UserFullname LIKE '%" + TextBox1.Text + "%'";

When your mate comes back from holidays, suggest he read up on injection attacks and/or characther-escaping.

jc

TowerMan

Thanks for all the help guys. Im improving a small bit at a time:o

Sorry bonkey how is your post helpfull to my query?

Merrion

The code that you got off the guy at work is a security risk.
(i.e. you could put malicious code in the textbox and it would execute)

bonkey

TowerMan wrote:

Sorry bonkey how is your post helpfull to my query?

Its not directly helpful to your query at all. However, if this is production code, then there are problems with it which you will find by reading up on the stuff I've mentioned.

I suggested that you tell the guy who wrote it to read up on the stuff because I originally made the (probably incorrect) assumption that this was some code in use in your workplace which someone else wrote and which you now need to maintain.

On second reading, I'm now more of the opinion that its not workplace code, but rather code given to you by a co-worker for whatever reason you need it. So maybe he doesn't need to read up on this stuff because he wasn't trying to write production-quality code....

At the end of the day, there are serious issues with the code you offered, and I've given you all the information you should need to find out what they are. I didn't go into more detail so I'd neither be doing your job nor your homework for you, but rather giving you pointers to learn a bit more about the stuff. Its up to you if you want to use those pointers.

jc