Peter's Gekko

public Blog MyNotepad : Imho { }

SELECT * WHERE % ORDER BY why ?

A sql select query looks something like this :

SELECT MyCol FROM MyTable WHERE MyCol = 'A' ORDER BY Mycol

It has a couple of parts

SELECT FROM specifies the output columns
WHERE specifies the rows
ORDER BY specifies the sort order of the output rows

What puzzles me is that the order of these three parts is fixed. I cannot interchange parts and state my query like this

SELECT MyCol FROM MyTable ORDER BY Mycol WHERE MyCol = 'A'

In my simple view that would be no big deal for the SQL parser. But it (the SQL server one) will throw an error at you. I think this is a pitty; else it would be oh so easy to further specify the WHERE clause like:

[AirCode]
string mySQL = "SELECT MyCol FROM MyTable ORDER BY Mycol WHERE MyCol = 'A'"
if (TextBoxName.Text != "")
mySQL+= " AND Name Like @Parameter1"

Am I overlooking something or is this just sql syntax rigidity ?

Published May 25 2005, 12:50 PM by pvanooijen

Filed under: Programming, Data

Comments

Brendan Tompkins said:

I'm not sure about the possiblity of SQL server doing this, but wanted to point out a big possible SQL injection attack in your air code...

What happens if someone passes in

'junk' go drop table MyTable go

in for the param?

Am i being persnickedy? I do know it's air code, but even air code can be air hacked, right?

;)

B

# May 25, 2005 1:20 PM

pvanooijen said:

It's a parameter so it's safe against an injection :
http://codebetter.com/blogs/peter.van.ooijen/archive/2005/04/08/61674.aspx

The full code would be
string mysql = "SELECT MyCol FROM MYtable ORDER BY MyCol WHERE MyCol='A'";
if (TextBoxName.Text != "")
mySQl+= " AND Name LIKE @Param1"

sqlCommand cmd = new sqlCommand(mySql);
if (TextBoxName.Text != "")
cmd.Paramters.Add("Param1", TextBoxName.Text);

Running this code crashes on creating the command. "Incorrect syntax near the keyword 'WHERE'"

# May 25, 2005 1:54 PM

Brendan Tompkins said:

Ah.. I see, I didn't notice that this was C# code, I was thinking that you were doing dyn sql in a sproc.

SorrY!

# May 25, 2005 1:59 PM

Joe said:

I find that it's just as easy to insert a unique string into your base SQL and use string replacement.

"Select Mycol FROM MyTable $WHERECONDITION$ order by Mycol"

Then build wherecondition separately and do a simple string replace. It also makes your code easier to read if you are generating sql on the fly.

# May 25, 2005 2:39 PM

Frans Bouma said:

The reason is that select and from parts can define elements which are referred to in the where part and order by part. (and group by / having part!). I.o.w.: it's weird to refer to something that's defined later on, hence the fix.

# May 25, 2005 2:45 PM

pvanooijen said:

Joe,

That's what I am doing. The downside is that you have to start with something to replace in your sql. And as long as it is not replaced yet I have no valid sql. Which has its drawbacks, espaecially when constructing the sql with a visual designer.

Frans,

I buy that.

# May 25, 2005 11:46 PM

Thomas Eyde said:

The string.Format() method gives you placeholders to replace on the fly. Then you don't have to know where the placeholder is, and the sql syntax doesn't matter anymore.

# May 26, 2005 2:35 AM