Tech Tips, Tricks & Trivia

This answer in dba.stackexchange.com puts in nicely -

Whether you use SQL statements or stored procedure doesn't matter. What matters is whether your SQL uses parameters or concatenated strings. Parameters prevent SQL injection; concatenated strings allow SQL injection.

The OWASP wiki has a more detailed explanation

The Stack Exchange family of websites lets you sort & view top voted questions in a specific area of interest identified through tags. They provide a RSS feed that you can keep following through a RSS Feed Reader like Google Reader, to stay abreast of hot questions in your favorite programming area.

The RSS feed for the top voted questions with the sql-injection tag on dba.stackexchange.com looks like this - http://dba.stackexchange.com/feeds/tag?tagnames=sql-injection&sort=votes

Labels: Security, SQL Server

From my old notes - a list of sample database scripts (.SQL files) that you can use for your SQL Server  experiments:

• Pubs
• Northwind  (download link:  archive.msdn.microsoft.com)   
• BBC Countries Dataset
• CIA World Factbook
• Nobel Prize Winners
• Music
• Movie Database
• ACME products
• Top of the Pops
• Musician
• Southwind (download link: SQLzoo.net)
• WordNet lexical database from Princeton University (download link: packaged as a SQL Server script by ObjectGraph )
• Stack Exchange dump of public data 

Labels: SQL Server, Trivia

In the Pluralsight course "SQL Server Questions and Answers", Pinal & Vinod debunk some SQL Server misconceptions with examples and highlight uncommon facts -

• Use SCOPE_IDENTITY() instead of @@IDENTITY or IDENT_CURRENT("tablename") to get the latest IDENTITY value for the table in the session
• @@IDENTITY returns the last IDENTITY value produced on a connection
• SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection (explicity created by you rather than a trigger) and by a statement in the same scope
• IDENT_CURRENT("tablename") returns the last IDENTITY value produced in a table, regardless of the connection
• Identity column value can be positive or negative
• Identity column can be reseeded with DBCC CHECKIDENT command
• Deleting records from table using DELETE does not reset Identity values whereas deleting records from table using TRUNCATE resets Identity values.
• TRUNCATE statements are logged and can be part of transaction. Such transactions can be rolled back.
• Executing TRUNCATE is not possible when a table is referenced by a foreign key or if the table is used in replication or with Indexed views
• WHERE can be used with SELECT, DELETE, UPDATE statements whereas HAVING is used along with GROUP BY clause in a SELECT statement
• The order of the conditions in the WHERE clause does not affect index used. This can be verified from the Execution plan.
• If parenthesis is used with a combination of OR & AND conditions, the result & index usage may vary
• Table variables & Temporary tables both exist in TempDB
• Table variables too can have a clustered index but they do not participate in the Transaction context.
• A Table variable cannot be assigned to another Table variable
• A Table variable cannot be truncated
• Stored procedures are not compiled when created.
• Stored procedures are compiled on first run & this can be verified by watching the SP:CacheInsert event in Profiler.
• Using Filtered Index (uses WHERE in an UNIQUE INDEX) which is new in SQL Server 2008, it is possible to have multiple nulls even when UNIQUE constraint is imposed.
• In SQL Server 2008, date and time can be stored independent of each other in a db using the DATE & TIME datatypes
• DateTime2 datatype has a precision of 100th of a nanosecond.
• Precision of SmallDateTime datatype is 1 minute
• Multiple UNIQUE constraints can be defined on a table
• A quick way to populate table rows - 
CREATE TABLE Test (id INT IDENTITY(1,1), Long_Name CHAR(7500) DEFAULT 'dummy') 

INSERT INTO Test DEFAULT VALUES; GO 15; --inserts 15 rows

• fn_dblog is an undocumented system UDF that lets you to read from your transaction log  
• Lock duration is dependent on the ISOLATION LEVEL (READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE)
• When a nested transaction is rolled back it always rolls back to the outermost BEGIN TRANSACTION statement (unless SAVEPOINT is used)
• DENY takes precedence over GRANT except at a column level GRANT
• Blocked process report Event under Error & Warnings section of Events Selection tab in the Trace Properties dialog box in Profiler helps in analyzing Blocking issues
• Recent Expensive Queries tab within Activity Monitor in SSMS shows queries that you can optimize.

Labels: SQL Server

The JavaScript alert method is something that most web developers use to debug their code. However, now with all popular browsers incorporating Developer tools within them, there are easier ways to debug client-side code. Firebug is an optional add-on for Firefox that adds richer features to those natively available & probably the motivator for browsers to develop their own Developer tools. I guess, it was IE that started using F12 as keyboard shortcut to start up Developer tools and now that's synonymous with Developer tools on almost all browsers.


Within the Developer tools, a Console panel exists for executing script statements on-the-fly. You can make use of Console methods to expose information that is flowing through your scripts. The console.log method can be used as an alternative to the obtrusive JavaScript alert method to track what's going on within your code without having to click OK for every dialog box that alert throws up. This will work only if you have the Console opened though.


So to remove your debugging code when you deploy to production, use this:
if ( window.console ) {
  // console is available
}


While console.log is one of the more common methods supported in popular browsers(IE, Firefox Firebug, Chrome, Safari), there are other methods as well for which support may vary -

• console.info
• console.warn
• console.error
• console.assert

Ctrl+Shift+J shortcut lets you jump to the Console panel in Chrome.

Labels: Browsers, Chrome, Firefox, IE, Javascript

Did you know, India has 120 million Internet users & the Indian e-commerce market is worth $7 billion? Travel accounts for over 80 percent of the e-commerce market.

Forbes believes "the rise of e-commerce is significant because it shows the limitations of physical retailing and the rise of aspirations of small town India. Between the large unorganised retail (kiranas and unbranded supermarkets) that serves the masses and modern retail that serves the aspirational class in large cities, there lies a thin wedge of aspirational classes who live in dusty towns. The economics of physical retailing have always left them short changed."

Yet another indicator of the rise of e-commerce is the growing number of comparison shopping sites -

• MySmartPrice - for books & gadgets
• 90DI - finds fastest & cheapest travel options (train, bus & flights)
• Redbus - choose travel options from over 350 bus operators
• IndiaBookStore.net - search engine for books available at major Indian online book stores.

If you know of any more much websites, please leave a comment

Labels: India, Websites