Here is how you go about creating a database and running a script file from MySQL command line:

To create a database:

mysqladmin create your_database_name -u your_username -p your_password

To run a MySQL script file from command line:

mysql -u your_username -p your_password < your_script_name.sql

Just because I forget:

• Open the table in SQL Server Studio
• Find the value you want to set NULL
• Click on the value
• Press Ctrl+0 (zero).

The other day my co-worker was showing me a tool that he has created to introspect a given database. So I went looking for an easier way to do it in ColdFusion and found cfdbinfo.

CFDBINFO allows you retrieve information about a data source, including details about the database, tables, queries, procedures, foreign keys, indexes, and version information about the database, driver, and JDBC.

The CFDBINFO tag returns a query object that contains information about a database. The query object varies, depending on the value that you specify in the type attribute. You can further filter information about specific tables, columns, or stored procedures by using the ‘pattern’ attribute.

Syntax:
<cfdbinfo
 datasource="data source name"
 name="result name"
 dbname="database name"
 type="dbnames|tables|columns|version|procedures|foreignkeys|index"
 table="table name"
 pattern="filter pattern"
 password="password" username="username">

Examples:

Database product name and Version, driver name and version, JDBC major and minor version:
<cfdbinfo type="version" datasource="cfartgallery" name="dbdata">

All Databases for a given Datasource:
<cfdbinfo type="dbnames" datasource="cfartgallery" name="dbdata">

All Tables for a given Database:
<cfdbinfo type="tables" datasource="cfartgallery" name="dbdata">

All Columns for a given Table:
<cfdbinfo type="columns" table="artists" datasource="cfartgallery" name="dbdata">

All Foreign Keys for a given Table:
<cfdbinfo type="foreignkeys" table="orders" datasource="cfartgallery" name="dbdata">

All Index for a given Table:
<cfdbinfo type="index" table="artists" datasource="cfartgallery" name="dbdata">

All Stored Procedures for a given Database:
<cfdbinfo type="procedures" datasource="cfartgallery" name="dbdata">

Enjoy!

Recently I had to send a code snippet to a mate and instead of sending a SQL database script I decided to use the in-built database called Apache Derby.

Here is how you go about creating a new ColdFusion Derby datasource and database:

1. In the Add New Data Source section of the CF admin, Name your data source and Select “Apache Derby Embedded” driver and click Add
2. Specify a folder name (not the full path) in the “Database Folder” field.
3. Click the “Create Database” checkbox and hit Submit
4. Depends on how your CF is setup but it should have created a new database folder in C:ColdFusion8db
5. Once the database and datasource are created, you can use the CREATE TABLE to create tables

Now to distribute the Derby database all you have to do is send the newly created Database folder. They need to just drop the folder in their DB folder and create a new datasource and Bob’s your Uncle.

Further Reading:

Charlie Arehart’s excellent post: Getting Started with the Apache Derby Open Source Database in CF8

HP Web Security Research Group in coordination with the Microsoft Security Response Center has created a Free downloadable tool called Scrawlr which is is short for SQL Injector and Crawler.

Scrawlr crawls a website to detect SQL Injection vulnerabilities on pages that hackers can discover using a simple crawler or Google query. It supports many file types.

Limitations:

1. Only tests for SQL Injection vulnerabilities and only tests the query string parameters of  URLs
2. It does not submit forms (no POST parameters) nor audit them
3. It will crawl a max of 1500 URLs
4. It also does not interpret JavaScript or Flash
5. Does not keep state or use Cookies
6. No authentication support

Related Links:

1. The HP Security Laboratory Blog Post
2. Download Scrawlr (Registration Required)
3. Scawlr FAQs

Pinal Dave has written an excellent post on how you can import a CSV or comma delimited file into SQL Server. Pinal has extensive SQL Server & Coldfusion experience. If you use SQL Server you must subscribe to his blog.