further discussions about parameterized queries (PQ)

[netnewb]

There are times when parameter queries won't work. They are not very flexible.

Let's say you make a custom query dialog, for flexible reporting. User enters one/more parameters: one/more customer names, a dollar amount, less than or greater than (e.g. order amounts <$100, or >$100), and one of several possible sort orders. You can build that complex query easily and briefly by building up a SQL string, but unless I'm mistaken, parameters won't do it. (Well, I suppose you could have many different parameterized queries, depending on which parameters the user chooses to supply, but that grows huge fast with permutations)

Also, the example you give is ugly code; bad primarily for being a classic jumble. The embedded functions ought to be separated out, assigned to temporary variables; this would ease both reading and debugging. It seems to me that although parameters are more technically correct, and will avoid a few types of problems, they're not nearly as elegant (quick and tidy and self-contained) as building the sql string. It doesn't take much to build the sql string correctly and safely, anyway.

 

[Invisionsoft]

I'm sure I could build an implementation of paramters in PHP (ah... str_replace ). Thinking about it, ASP.NET should be entirely duplicable, except ActiveX.

As for my 4GB drive: It's fine. I have a removable 1gb SD which I store my dev stuff on. It's not my main machine by the way. It's faster than my main PC. My main machine is Vista HP, 320 GB HD and 2GB RAM. Yes, that's slower than my tiny laptop.

I have Paint.NET, Firefox and VB on my tiny laptop: What more do you need?


- James

 

[JohnH]

SQL itself doesn't have parameterized queries, it's the .Net controls that has it and fills in the blanks (replaces your parameters with the actual value(s))

If you want to get into manually sanitizing the user's input that's fine, MS's .Net's parameter's are there to do most of it for you as well as making it easier to follow the code.

No, the query is sent parameterized with values separate. If you don't parameterize the server will try to parameterize it first, then add the query to its execution plan, then run it with the values - but this will not prevent injections. It is also difficult to manually validate input "value" for additional commands when doing the string thing, but when using parameters it is not possible to add commands since the query is given and not changeable. The .Net parameters provide type validation both at design time and runtime and handles how values of various types are converted between database and data access layer.