| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2002-11-21 : 21:26:27
|
| I thought about keeping the BEER PASS thread alive, but then thought maybe it'd be better to start a new one. Here are a few thoughts I have so far (just wrapping up day 2 of 3).1) The MS guys are REALLY tight-lipped about Yukon. I wish they'd tell/show us more. We saw more today than yesterday. Maybe we'll see even more tomorrow. I hope so. Today we saw some cool OLAP/BI stuff. I have hardly done any OLAP, but Lance (LHARRA) was pretty excited by what he saw, which was a demo of the OLAP (or BI or whatever the right acronym is today) debugger. Looks like it'd be really handy.2) We're having fun making friends and unofficially recruiting new folks for SQLTeam. Hopefully Denise will write and article on how she has used Profiler without the GUI (and therefore without a lot of the overhead, too).3) I sat in on a session called T-SQL Tips & Tricks with Itzik Ben-Gan. Dude made my head spin, but I came away with about 3 ideas for new articles. This was a LOT more than I expected it to be. Now, if I can only make the time to write them...I'm sure there's more to say, like some of the cool tools demo'd here at the Exhibit Hall. I'm pretty excited about Lumigent's new tool in beta called Entegra. I've promoted it so much in the last 24 hours, I should get a free copy from them, or something.Hopefully others who are here will pick up on this thread and add some comments of their own.---------------------------------------------------The more you know, the more you know you don't know. |
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2002-11-23 : 20:14:36
|
| The cool demo we saw on BI at PASS was debugging a cube with calculated members.I attended the session a sql hacking. The main theme is if you follow the best practices for SQL Server you are secure. The only caveat he presented was a sql server connnected to the web. The presenter recommended turning off the SQL Agent (just to many ways to escalate priviliges.I attended a great session on exposing sql stored proc's as a web service via SQLXML3.0. (I will be trying this ar work when I get back) Learned a lot about T-SQL optimization. I am going to look at all the if else logic I have. It will optimize all the code it passes over. i.e. You have an if else statement in your stored proc. You call the proc and only the else part executes. The optimizer will oprimize the code that is not excuted.Lance Harra |
 |
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-24 : 11:11:06
|
| I really wish I could've gone, but small things like membership, money, and distance prevents nice long trips to conferences.I thought the optimizer would only optimize the code that would be executed? (or was it a type in your post?)I don't remember my source, but I remember reading that for that very reason some people like to delegate work to other stored procedures whenever they start to introduce IF-ELSE statements. (Of course, there are times when this would slow things down or be un-workable. I'm thinking about temp tables vs table variables.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2002-11-24 : 18:50:21
|
| The optimizer compiles a query plan based on the first execution of the proc. If the first execution sends you down the IF path, for example, then the query plan in cache is the one optimized for that execution. If the proc is later called with an input that sends it down the ELSE path, it will still use the plan that is in cache, even if that is not efficient or the best option. So yes, an approach that can protect you from this is modularizing the code so IF calls one nested proc and ELSE calls another.And Mark, thanks for the not-so-subtle prompting ;-) After our deployment is done in December, I'll try to scribble something.Denise |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-11-24 : 20:34:02
|
Denise, welcome to the team. Actually, this was pretty subtle. I was thinking I might have to email you the link to this thread, but now I don't think that will be necessary. Good luck on your rollout!Regarding the Hacking SQL Server session, that was GREAT! One of the best I attended I think, maybe because I was really interested, and it was real-world experience. This presentation was based on a hacker challenge they had at some previous (non SQL specific) conference. They claim that the hackers never got the data. One point of interest was that of the thousands of attacks attempted, a large portion of them were SQL Injection attacks (click here for a primer on the topic).------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2002-11-25 : 10:11:47
|
Well, I did have some trouble remembering the name of the site since I only heard it mentioned once or twice last week. But I managed to find it all on my own. I know graz will be thrilled to see me post  Did I miss big fun Friday night?Denise |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-11-25 : 12:00:57
|
With all those variations you came up with, I can see how it might be confusing. I was sworn to secrecy that I would not tell stories about what Graz did. But Michael and Sara on the other hand... When I left, several of the folks were headed out with the other MVPs. I didn't see any bad headlines in the paper, so...------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2002-11-25 : 13:30:45
|
| Denise glad to see you were able to find the site.:)If you have the chance to here Kimberly Tripp speak. She gave a great presentation on optimizing T-SQL code.Lance Harra |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2002-11-25 : 13:34:33
|
| Forgot to mentionI agree with AjarnMark about SQL injection. It was amazing how easily a site was hacked and priviliged accounts added.I am pushing our developers to look at QUOTENAME to help protect from sql injection.Lance Harra |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-11-25 : 15:27:17
|
quote:
Well, I did have some trouble remembering the name of the site since I only heard it mentioned once or twice last week. But I managed to find it all on my own. I know graz will be thrilled to see me post Did I miss big fun Friday night?Denise
Well hello! Friday night turned into a late one. You'll have to ask Scott about the Tequila shot he mostly did. That was pretty entertaining! And it reminded me why I never touch the stuff.I am suffering from alcohol withdrawl though. I think I might check into Betty Ford. I need a vacation to recover from the conference.And I discover that the Chiefs were playing in Seattle on Sunday. I should have just stayed and watched the game! Oh well, maybe next year.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-25 : 18:09:44
|
| Wow - SQL Injection - looks like I've never been vulnerable because I've always had a "sqlify" function which I pass all values into (which are destined for SQL strings) - which does a replace("'","''"). However - what I'd love to know is - does anyone out there have a definitive "this is how you should do it"? Is the replace the only thing that needs to be done?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-25 : 19:29:14
|
| You can cut down a lot on SQL injection by using stored procedures and avoiding dynamic SQL, both as much as possible. Review your existing code, both on the SQL Server and in your apps, for any places where SQL syntax could possibly be altered by passing a particular parameter/value. If the syntax is locked down and parameters can only pass data values, then you're in fairly good shape. |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2002-11-25 : 21:06:56
|
| Lance, I was able to attend Kimberly Tripp's seminar in August. It was great. I agree, she teaches you a lot about optimization. That's actually where I learned the IF ELSE business. I didn't go to any of her sessions last week, because I figured she had covered most of the material in August.Graz, hi! Nice place you've got here. If you guys moved on to tequila shots, I'm sure it got interesting. Sorry I missed it. |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2002-11-27 : 12:30:05
|
| A couple of the presenters recommended using the quotename function on the sql side to help sqlify your statements and help prevent sql injection.Lance Harra |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-11-27 : 17:03:06
|
quote:
I attended a great session on exposing sql stored proc's as a web service via SQLXML3.0. (I will be trying this ar work when I get back)
Do you know if the materials from that SQLXML presentation can be found on the web somewhere? I'd be most grateful for any links on the subject!!!Also, if anyone has used SQLXML in a production environment, I would love to hear about your experience with it. Actually, I should probably move this discussion to Developer forum.<edit>Just opened a topic in Developer forum: http://sqlteam.com/Forums/topic.asp?TOPIC_ID=21987</edit>Edited by - izaltsman on 11/27/2002 17:15:45 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-27 : 17:17:00
|
quote:
I should probably move this discussion to Developer forum.
If you do - could you post a link back here? - Thanks--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
PASS Comments