Tag: Databases

Categories
Uncategorized

If You Speak Database, Science Needs Your Brain!

 database brain

If you’re in the Greater Toronto Area, have basic knowledge of database queries and want to help a grad student with a research project, Zuzel Vera Pacheco, one of Greg Wilson’s students at University of Toronto, needs to borrow your brain! In exchange, you’ll get a chance to win a $100 Best Buy gift card.

Here’s her description of the project:

Want to win a $100 Best Buy gift card? Do you have basic knowledge about database queries? If so, I need you!

Subjects are needed to take part in a study concerning the visualization of database queries. Participants will be asked to draw diagrams that represent the execution of database queries or to determine what queries are represented by a set of diagrams. This study will help design a tool intended to help expert and novice programmers to design and debug such queries. The time needed for the study will range from 30 minutes to an hour, and can take place in the Bahen Centre at the University of Toronto or elsewhere in the Greater Toronto Area.

A basic understanding of relational databases and database queries is required. The examples will contain queries in SQL and other programming languages like Ruby or Python. The participants should be fluent/conversant in English.

Participants who complete the study will be entered into a random draw for a $100 Best Buy gift card. The odds of winning this prize are 1 in 30.

If you think you can help Zuzel with her project, drop her a line!

This article also appears in Canadian Developer Connection.

Categories
Uncategorized

SQL Injection and the “Flintstones/Jetsons” Way to Deal with Licence Plate Cameras

Renault with a banner across its bumper reading "ZU 0666', 0, 0); DROP DATABASE TABLE LICENCE;"Click the photo to see it at full size.

“Flintstones/Jetsons” is a term that Mark Mothersbaugh from Devo uses to describe technology solutions that are a combination of low- and high-tech. It’s probably an apt term for what the driver of the Renault in the photo above is doing to foil licence plate cameras. If the “Jetsons” part – the SQL injection attack comprising the text on the banner on the bumper – doesn’t work, the “Flintstones” approach of physically covering up the licence plate will.

SQL Injection-a-Rama

No quick tour of SQL injection is complete without mentioning this classic XKCD comic, Exploits of a Mom. If you’ve ever heard someone use the phrase “Little Bobby Tables” when talking about databases and security, here’s where it comes from:

The classic "Little Bobby Tables" XKCD comic.

"SQL" with a syringe sticking through it

Want a good introduction to SQL injection attacks? Start with SQL Injection Attacks by Example at Steve Friedl’s Unixwiz.net Tech Tips. It walks you through the steps of an SQL injection attack, where a cracker (note that I said “cracker” – there are hackers and crackers, and there’s a difference) uses a combination of deductive reasoning and unexpected, unsanitized input to get unintended results from the database.

Also worth checking out:

Here’s an enjoyable presentation by Joe McCray on Advanced SQL Injection, which he gave at the 2009 LayerOne conference. He likes to drop the “f-bomb” and “s-bomb” every now and again while presenting, but if you don’t mind a little salty language, it’s a good security talk:

(You can download the slides from Joe’s presentation in PDF format here.)

This article also appears in Canadian Developer Connection.

Categories
Uncategorized

Area Man Gets His Own Channel 9 Show

ryan_mcminn

Ryan McMinn – co-founder of the Toronto-based development shops Unspace and M7 Database Services turned Sith Lord with The Empire’s Access Team – co-hosts the newest show on Microsoft’s Channel 9: The Access Show. Ryan hosts The Access Show with Clint Covington and in the first episode, he and Clint talk about Access Services, a part of SharePoint 2010, which you can use to create new databases with forms and reports that run in the browser.

Ryan, on behalf of all of us at Microsoft Canada and the Toronto geek scene, I’d like to congratulate you on an excellent first episode and salute you with a left outer join on a flaming sword!

Go ahead – watch Ryan’s show now!

(At this point, you might be tempted to ask me “Hey, Joey: you seem like the sort of person who’d like to have his own show talking about Microsoft tools and tech. Why don’t you have one?” My response would be simply to say “Wait.”)

This article also appears in Canadian Developer Connection.

Categories
Uncategorized

Tired: DateTime. Wired: DateTimeOffset!

You’d think that with 10,000 years of date- and time-keeping under our belts, it would be easy to keep track of dates and times in a modern-day database. It’s a little trickier than you might think, according to The Death of DateTime?, an article in Bart Duncan’s SQL Weblog.

The gist of the article is pretty simple: if you’re using SQL Server 2008 and want to store dates and times unambiguously, use the datetimeoffset type (introduced in SQL Server 2008) rather than the traditional datetime.

Why? Because datetimeoffset is datetime with these key differences:

  • The time value is stored internally in an unambiguous UTC format
  • The local time zone offset is stored along with the UTC time
  • It is capable of storing more precise times than datetime

DesktopDuncan recommends that if you’re storing data in SQL Server 2008, you should almost always store date-and-time values in datetimeoffset rather than datetime. It’s a good idea; I’d go even farther and suggest that if you’re programming using .NET 3.5, you should make use of the corresponding DateTimeOffset type instead of DateTime. You can read more about .NET 3.5’s DateTimeOffset type in this entry in Dan Rigsby’s blog titled DateTime vs. DateTimeOffset in .NET.

When might you want to use datetime? Duncan suggests that you should use it in those rare cases when you want to store time ambiguously. The example he provides is: “if you wanted a column to record the fact that all stores in a chain should open at 8:00am local time (whatever the local time zone may be), you should use datetime.”

Thanks to Brent Ozar for the link!

Categories
Uncategorized

The Manga Guide to Databases

Maybe we’re seeing the start of a strange new manga trend in books. First came The Adventures of Johnny Bunko: The Last Career Guide You’ll Ever Need, and now there’s The Manga Guide to Databases:

Cover of "The Manga Guide to Databases"

Here’s the publisher’s write-up of the book:

Want to learn about databases without the tedium? With its unique combination of Japanese-style comics and serious educational content, The Manga Guide to Databases is just the book for you.

Princess Ruruna is stressed out. With the king and queen away, she has to manage the Kingdom of Kod’s humongous fruit-selling empire. Overseas departments, scads of inventory, conflicting prices, and so many customers! It’s all such a confusing mess. But a mysterious book and a helpful fairy promise to solve her organizational problems—with the practical magic of databases.

In The Manga Guide to Databases, Tico the fairy teaches the Princess how to simplify her data management. We follow along as they design a relational database, understand the entity-relationship model, perform basic database operations, and delve into more advanced topics. Once the Princess is familiar with transactions and basic SQL statements, she can keep her data timely and accurate for the entire kingdom. Finally, Tico explains ways to make the database more efficient and secure, and they discuss methods for concurrency and replication.

Examples and exercises (with answer keys) help you learn, and an appendix of frequently used SQL statements gives the tools you need to create and maintain full-featured databases.

(Of course, it wouldn’t be a royal kingdom without some drama, so read on to find out who gets the girl—the arrogant prince or the humble servant.)

This EduManga book is a translation of a bestselling series in Japan, co-published with Ohmsha, Ltd., of Tokyo, Japan.

The book will be available on December 1st, just in time to order as a Christmas present for someone who’s into both programming and manga.

Categories
Uncategorized

Putting Your Database Under Version Control

In his latest article on Coding Horror, Jeff Atwood talks about the importance of getting your database under version control and links to K. Scott Allen’s series of articles on the topic:

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging

Lucky me, I’ve got Rails migrations, which are suitable for small teams. Other development platforms will require different approaches, some of which are covered by Allen’s articles. If there’s a topic that’s screaming for a book or website, this is it!