gregs

Just came across this neat trick that uses SQL to generate XML on the fly:

The above will create an XML structure that has a root value of geoAreas and child elements of geoArea for each row found.

Courtesy of Slashdot a reminder that you should also worry about other types of injection attacks. Don't you just wish that everybody would play nice?

UPDATE

In a similar vein, Nadav Samet covers off in more detail Cross Site Scripting attacks, how they work and what you can do to protect your site.

Charlie Arehart has a handy little article published in this months CFDJ on using Microsoft's Log Parser tool, which allows you to write SQL like statements against your CFMX log files. Couldn't find a publicly accessible link, but if you keep an eye on his articles page, I am sure he'll update it as soon as it is available.

Update

Charlie dropped me a note last week to let me know that the article can be found on-line here

I just started using MSDE for local development again and after creating my first DSN through the ColdFusion administrator I encountered the following problem:

First thing I checked (well second after making sure I had provided the right connection details) was to see if anything was listening port on port 1433 by using the handy "netstats -a" command. And there wasn't, so a quick Google search turned up this gem from Robi Sen. Sadly this tool didn't help me as apparently my version didn't need it, but a comment did point me to a another link. But sadly it didn't help me either, but the comment did give me an idea. When I went to check my network set up for MS SQL server, sure enough TCP/IP was not listed... What followed was a moment of banging my head sharply on the table.

Right changes applied, restarted the server, verify connection and...

Well this one only took a few seconds to fix: Windows Authentication only was enabled! Simply open the properties of your server once more, check the security tab and you should see that the option for Windows Authentication is selected. Just change that to SQL server and Windows and you are ready to go...

I guess this is very much a case of damned if you do and damned if you don't for Microsoft as they seemed to have tightened up the security for MS SQL server (I won't even go into the nuisance of trying to install MSDE and being prompted to set an SA password before starting with no indication how to do so), but surely we should be given the option at install time to set these things? Or maybe it's just the MSDE?

I have been doing a lot more database based work recently and have learned one or two pretty useful techniques. Here is an extremely useful one: Running an update based on a select statement.

In the past whenever I could have wanted to do a bulk update on a table based on certain criteria, I would have run a select statement to retrieve a bunch of columns first and then programmatically looped over the result set and updated the values. But there is a much, much easier and way more efficient way of doing this. Just take a quick look at the statement below.

It was constructed very much the way I would have normally approached the problem. First I wrote the select statement in Query Analyzer, i.e. SELECT * FROM FROM someUsersTable INNER JOIN someOtherJoinTable ON someUsersTable.userID = someOtherJoinTable.userID WHERE (tbl_Users.userEmailPrivate = ''). This gave me the result set I wanted to work with. Next I quite simply removed the SELECT statement replaced it with a corresponding UPDATE statement. So easy when you know how and the performance should be way better than before hand, because rather than making multiple trips back and forth to the database server from the web application it's all handled by the database server in one swoop!