Click here to Skip to main content

The Weird and The Wonderful

The Weird and The Wonderful forum is a place to post Coding Horrors, Worst Practices, and the occasional flash of brilliance.

We all come across code that simply boggles the mind. Lazy kludges, embarrassing mistakes, horrid workarounds and developers just not quite getting it. And then somedays we come across - or write - the truly sublime.

Post your Best, your worst, and your most interesting. But please - no programming questions . This forum is purely for amusement and discussions on code snippets. All actual programming questions will be removed.

Re: String.Format???

PIEBALDconsult17-Jul-10 2:54

17-Jul-10 2:54

_Maxxx_ wrote:
No - you usually back up databases as a whole - SPs, views, tables, data, keys, foregin keysm constraints etc.etc.etc. and restore them the same

That's my point, you would lose recent code changes and have to reapply them.

_Maxxx_ wrote:
+ " and active = true".

I've never written code like that. Currently I use C# verbatim strings.

cmd.CommandText = @"
SELECT 
blah
, blah
, blah
FROM table
WHERE something
AND something else
" ;

Having linefeeds in the SQL makes them easier to read when logged with error messages.

Lately I've been thinking about storing SQL statements as resources.

_Maxxx_ wrote:
keep an open mind and look at the pros and cons.

As I have, but also remember that many of the features we've discussed are unique to SQL Server. I use a lot if different database systems and try to stay as database-agnostic as I can.

Re: String.Format???

_Maxxx_18-Jul-10 1:09

18-Jul-10 1:09

PIEBALDconsult wrote:
That's my point, you would lose recent code changes and have to reapply them.

? I don't follow?
You release a change to the database, which is backed up that night. Unless you want to roll back the change before the next backup, I don't seee the problem - or am I missing something?

PIEBALDconsult wrote:
Lately I've been thinking about storing SQL statements as resources.

Good idea. You could write them as text and then save them to the database - call them, erm, I dunno, well, they're SQL procedures and you'
re storing them - hey call them Stored Procedures Smile | :)

Smile | :)

___________________________________________
.\\axxx
(That's an 'M')

Re: String.Format???

PIEBALDconsult18-Jul-10 4:01

18-Jul-10 4:01

I mean add or modify a stored procedure, then have to restore from an earlier backup. (Consider a client who doesn't have a well-run shop.)

If I stored my SQL in a database, it wouldn't be in the database they act upon; just as I have a database that contains nothing but user-defined functions which I can use on any of my databases. Plus it would allow for multiple versions of a statement. At any rate, I meant resources within the application.

Re: String.Format???

oggenok6417-Jul-10 5:07

17-Jul-10 5:07

No, i'm not working in the financial sector. And yes, my example was a reporting system having read-only access to everything - not just implementing a SP but also to your savings account Smile | :)

Smile | :)

Re: String.Format???

PIEBALDconsult9-Jul-10 5:02

9-Jul-10 5:02

tinko101 wrote:
SQL injection attacks

That's only due to this particular command not using parameters.

tinko101 wrote:
raise maintenance issues

Stored procedures raise maintenance issues too.

And how will you execute your stored procedure from code without having SQL in your code?

Re: String.Format???

Jason Christian22-Jul-10 9:00

Jason Christian

22-Jul-10 9:00

You can avoid the SQL injection attacks by using parameterized SQL, instead of String.Format. String.Format should not be used to create SQL statements. Parameterizing the query is a much better option.

Personally I find having the SQL in the code is more maintainable then having it buried in a SP in the database. I still have to call the stored proc, and code is easier (for me) to debug.

Re: String.Format???

CDP18029-Jul-10 0:44

9-Jul-10 0:44

In many cases a stored procedure would offer some benefits.

The real horror here is that the parameters were added with string formatting. Command parameters would have been a far better choice. This way, depending on where the parameters come from, the door is open for SQL injection attacks.

A while ago he asked me what he should have printed on my business cards. I said 'Wizard'.

I read books which nobody else understand. Then I do something which nobody understands. After that the computer does something which nobody understands. When asked, I say things about the results which nobody understand. But everybody expects miracles from me on a regular basis. Looks to me like the classical definition of a wizard.

Re: String.Format???

sergiogarcianinja9-Jul-10 4:47

sergiogarcianinja

9-Jul-10 4:47

I think in that case you can use a in code SQL, but using SQL parameters.
Then, in you SQL command you add the parameters.

Something like this:

string query = "select * from table where column = @value";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add("@value", textBox1.Text);

This SQL statments are cached (so if you execute it in short time intervals the SQL plan will be computed) and aren't vunerable to SQL injection.

Re: String.Format???

Jörgen Andersson9-Jul-10 4:59

Jörgen Andersson

9-Jul-10 4:59

How would you add the parameter for a query like: SELECT * FROM Table WHERE ID IN (123,124,125);?
Lists are a bit tougher to handle in a proper way...

"When did ignorance become a point of view" - Dilbert

Re: String.Format???

PIEBALDconsult9-Jul-10 5:06

9-Jul-10 5:06

Personally I wouldn't. I wouldn't use IN at all, I'd find a way to have a table on which to JOIN instead.

The statement you present is a symptom of a poorly implemented system.

Re: String.Format???

David Skelly9-Jul-10 5:54

9-Jul-10 5:54

There was a thread on here recently discussing this, comparing IN, EXISTS and JOIN. I can't find it now, but if I remember correctly there was a link on there to a blog from one of the SQL Server tech-heads that explained why and how the three are not interchangeable.

Re: String.Format??? [modified]

PIEBALDconsult9-Jul-10 7:31

9-Jul-10 7:31

That might be an interesting read; I'll take a look to see if I can find it.

On the other hand, JOIN can do what IN and EXISTS can do, but IN and EXISTS can't do what JOIN does.

P.S. I just searched the general database forum back to May 1 and didn't find it.

modified on Thursday, August 5, 2010 12:02 AM

Re: String.Format???

Jörgen Andersson9-Jul-10 10:23

Jörgen Andersson

9-Jul-10 10:23

That's because it wasn't in the general database forum.
You'll find it here.[^]

"When did ignorance become a point of view" - Dilbert

Re: String.Format???

PIEBALDconsult9-Jul-10 10:33

9-Jul-10 10:33

Ah, good, thanks.

Re: String.Format???

oggenok649-Jul-10 8:14

9-Jul-10 8:14

Using an SQL IN-clause is definetely not a design flaw. Forcing everything into JOIN's is on the other hand an odd self-imposed hinderence.

Re: String.Format???

PIEBALDconsult9-Jul-10 8:59

9-Jul-10 8:59

While it is true that one should "use the right tool for the right job", I have never used EXISTS (I have an Oracle background), and I have not used IN/NOT IN for many years, and never with SQL Server.

JOIN tends to scale better -- you may have an IN, EXISTS, or even a BETWEEN that has to be converted to a JOIN as the project becomes more complex; using a JOIN to begin with eases such maintenance.

JOIN allows you to configure a system by maintaining a table rather than modifying the code.

As with Jörgen's post, an IN with hard-coded values I especially discourage; they reek of the "magic numbers" code smell. A subquery on some table would at least improve maintainability.

Re: String.Format???

Jörgen Andersson9-Jul-10 10:48

Jörgen Andersson

9-Jul-10 10:48

PIEBALDconsult wrote:
As with Jörgen's post, an IN with hard-coded values I especially discourage; they reek of the "magic numbers" code smell. A subquery on some table would at least improve maintainability.

I think I might have been unclear in my post. I never said they were hardcoded, I only wondered if he knew an easy way to add a list as a parameter and gave an example with using a list. Assume that that this list is dynamic and comes from the application.

Normally I would add that list to a temporary table and make a subquery or a join on that table. But it would be nice to be able to add that list as a parameter.

"When did ignorance become a point of view" - Dilbert

Re: String.Format??? [modified]

PIEBALDconsult9-Jul-10 10:51

9-Jul-10 10:51

Jörgen Andersson wrote:
list as a parameter

Indeed, you have me wondering whether or not a DataTable may be passed as a parameter. Though I'm sure that if so, that only SQL Server would support it, so it wouldn't be a general solution. Sigh | :sigh:

Sigh | :sigh:

P.S. This[^] looks interesting.

P.P.S. And this[^].

modified on Friday, July 9, 2010 5:21 PM

Re: String.Format???

Jörgen Andersson9-Jul-10 11:06

Jörgen Andersson

9-Jul-10 11:06

Well, it seems that you can pass an array as a parameter to a stored procedure in both SQL serever[^] and Oracle[^]

"When did ignorance become a point of view" - Dilbert

Re: String.Format???

PIEBALDconsult9-Jul-10 12:29

9-Jul-10 12:29

Eureka!

In SQL Server 2008 (Express):
I created an Account table with ID (int) and Name (nvarchar) fields.
I populated the Account table with some records.
I created an IDdef User Defined Table Type with an ID (int) field.

In C# I instantiated a DataTable, added an ID (int) column.
Added some rows to the DataTable.

Then set up the following (db is an instance of one of my DALs, dt is the DataTable):

db.Command.CommandText = "SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )" ;
                
System.Data.SqlClient.SqlParameter p = 
    new System.Data.SqlClient.SqlParameter 
    ( "@IDs" , System.Data.SqlDbType.Structured ) ;

p.TypeName = "dbo.IDdef" ;

p.Value = dt ;

db.Command.Parameters.Add ( p ) ;
               
db.Open() ;
                
System.Data.IDataReader dr = db.Command.ExecuteReader 
    ( System.Data.CommandBehavior.CloseConnection ) ;

And it works! Big Grin | :-D

Big Grin | :-D

I then changed the statement to SELECT * FROM Account INNER JOIN @IDs IDs ON Account.ID=IDs.ID

and that works too! Jig | [Dance]

Jig | [Dance]

Jig | [Dance]

Jig | [Dance]

Jig | [Dance]

Re: String.Format???

Jörgen Andersson9-Jul-10 13:00

Jörgen Andersson

9-Jul-10 13:00

My wife is going to kill me, I'm supposed to put up new wallpaper in the livingroom tomorrow. Not sit in front of the computer again.

"When did ignorance become a point of view" - Dilbert

Re: String.Format???

PIEBALDconsult9-Jul-10 13:05

9-Jul-10 13:05

It'll keep.

The wallpaper, that is. Big Grin | :-D

Big Grin | :-D

Re: String.Format???

PIEBALDconsult9-Jul-10 15:05

9-Jul-10 15:05

I finally remembered to try SELECT * FROM Account WHERE EXISTS ( SELECT ID FROM @Param0 IDs WHERE IDs.ID=Account.ID ), it works.

It appears that one of the differences between IN/EXISTS and JOIN is that the JOIN sorts (or maybe the others do).

Re: String.Format???

Jörgen Andersson9-Jul-10 10:32

Jörgen Andersson

9-Jul-10 10:32

PIEBALDconsult wrote:
Personally I wouldn't. I wouldn't use IN at all, I'd find a way to have a table on which to JOIN instead.

A JOIN is a poor substitute for a NOT IN or NOT EXISTS. And they don't handle nulls the same way.

PIEBALDconsult wrote:
The statement you present is a symptom of a poorly implemented system.

Agreed, but sometimes that's what you have. Sigh | :sigh:

Sigh | :sigh:

"When did ignorance become a point of view" - Dilbert

Re: String.Format???

PIEBALDconsult9-Jul-10 10:45

9-Jul-10 10:45

But your example uses IN, not NOT IN. Big Grin | :-D

Big Grin | :-D

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.