Protection against SQL injection attacks in PHP.

Early on PHP had no good methods for escaping SQL, and until recently didn’t support parameterized queries. As a result a lot of documentation covers SQL queries without really addressing the issue, and a lot of older PHP developers are unaware of the enhancements made to prevent this type of attack.

PHP 4.3 introduced mysql_real_escape_string which escapes all potentially “bad” characters which could cause unwanted results in your queries. The link contains examples of how to use the function.

PHP5 includes the MySQLi (MySQL Improved) extention which provides a more enhanced API for accessing MySQL. The mysqli_stmt_bind_param function allows you to use parameterized queries. The link to the function provides an example of how to use parameterized queries.

Parameterized queries are generally considered safer than escaped strings, but that’s only in theory. mysql_real_escape_string currently escapes all known bad characters, and the only thing that would make it unsafe would be for another bad character to be discovered. But parameterized queries will go through a more complicated code path, and thus, more likely to be affected by a coding bug. So there’s really no security related argument which favors one over the other.

Some great advanced JavaScript videos

Douglas Crockford of Yahoo has made some excellent JavaScript lecture videos. He covers how to work around the issues in JavaScript to make it more scalable and easier to work with. I’ve yet to even see a book or anything else which could be purchased for money which delves into the details of the language at a depth of what Crockford does.

Although they are videos, I found it easy to follow by just letting it play in the background while working on something else. Of course there are many times you must switch over to the video to see the code he’s referring to, so it wouldn’t work well on just an MP3 player.

Douglas Crockford: “The JavaScript Programming Language” 1 of 4

Douglas Crockford: “The JavaScript Programming Language” 2 of 4

Douglas Crockford: “The JavaScript Programming Language” 3 of 4

Douglas Crockford: “The JavaScript Programming Language” 4 of 4

Douglas Crockford: “Advanced JavaScript” (1 of 3)

Douglas Crockford: “Advanced JavaScript” (2 of 3)

Douglas Crockford: “Advanced JavaScript” (3 of 3)

Finding “dead time” in a database of start and end times.

The following snippet will find “dead time” (e.g. time where no events are scheduled) in a database:

    1 select distinct dateadd(s,-1,starttime) as deadtime,"start" from sometable t where
    2  0=(select count(*) from sometable u where u.starttime < t.deadtime and u.endtime > t.deadtime)
    3 union all
    4 select distinct dateadd(s,1,endtime) as deadtime,"end" from sometable t where
    5  0=(select count(*) from sometable u where u.starttime < t.deadtime and u.endtime > t.deadtime)
    6 order by deadtime