USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TruncateLogs] 
       @databaseName nvarchar(1024)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(1000)
 DECLARE @logfilename NVARCHAR(1000)

 SELECT @logfilename = b.name
 FROM sys.sysdatabases a 
  INNER JOIN sys.sysaltfiles b
   ON a.dbid = b.dbid
 WHERE fileid = 2
  AND a.name = @databaseName

 IF @@rowcount = 1 BEGIN
 
  SET @sql = '
  USE ' + @databaseName + '
  BACKUP LOG ' + @databaseName + '
  with truncate_only
  dbcc shrinkfile (' + @logfilename + ', 1)'

  EXECUTE sp_executesql @sql
 END

END

Next, just add it to the loop in the original ColdFusion scheduled task script:

<cfstoredproc datasource="master" procedure="dbo.sp_TruncateLogs">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
</cfstoredproc>

I was also thinking about a SQL Server upgrade. I'm running the Express version, which is perfect for most small-medium web sites. One thing it doesn't have is a way to do maintenance. I've been doing manual backups. Well, after looking at the price of a SQL Server license, I decided to look for a good automated backup. I found a backup script online at http://www.mssqltips.com/tip.asp?tip=1174 and modified it to meet my needs (made directory name a parameter)

CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName nvarchar(1024),
@type CHAR(1) = 'F',
@directory nvarchar(1024) = 'C:\SQLBackup'
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(1000)
DECLARE @currentDate NVARCHAR(20)

SELECT @currentDate = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/',') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':',')

IF @type = 'F'
SET @sql = 'BACKUP DATABASE ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Full_' +
@currentDate + '.BAK'

IF @type = 'D'
SET @sql = 'BACKUP DATABASE ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Diff_' +
@currentDate + '.BAK' WITH DIFFERENTIAL'

IF @type = 'L'
SET @sql = 'BACKUP LOG ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Log_' +
@currentDate + '.TRN'

EXECUTE sp_executesql @sql
END

Next, I created my own stored procedure for deleting old database backups. Basically, in a "real" SQL Server, the backup task will delete backups back to a date you specify. We'll pass in the database name, type of backup, timeframe (in weeks), and the directory name.

CREATE PROCEDURE [dbo].[sp_RemoveOldBackups]
@databaseName sysname,
@backupType CHAR(1) = 'F',
@howlong integer = 4,
@directory nvarchar(1024) = 'C:\SQLBackup'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @type nchar(3)
SET @type = 'BAK'
if @backupType = 'L' BEGIN SET @type = 'TRN' END
DECLARE @backupPath nvarchar(2048)
SET @backupPath = @directory + '\' + @databaseName
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(ww, -1 * @howlong, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @backupPath, @type, @DeleteDate,1

END

Now, rather than use Windows scheduled tasks for this, I wanted a simple ColdFusion scheduled task. The stored procedures should be stored in the master database, so you'll need a datasource that will allow execute permissions on these two stored procedures. Finally, save the following into a ColdFusion file and store it in one of your web sites. Set the scheduled task to execute once a week.

<cftry>
<!--- List of databases to backup --->
<cfset variables.databases = "mydatabase,Northwind,myOtherDatabase" />
<!--- Path to the backup folder --->
<cfset variables.backupPath = "Z:\SQLBackup" />
<!--- How many weeks to keep backups --->
<cfset variables.howLong = "4" />

<!--- Loop through the databases --->
<cfloop from="1" to="#ListLen(variables.databases)#" index="variables.i">
  <cftry>
    <!--- First, backup the database --->
    <cfstoredproc datasource="master" procedure="sp_BackupDatabase">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
      <cfprocparam cfsqltype="cf_sql_char" value="F" />
      <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.backupPath#" />
    </cfstoredproc>
    <!--- Next, delete the old databases --->
    <cfstoredproc datasource="master" procedure="sp_RemoveOldBackups">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
      <cfprocparam cfsqltype="cf_sql_char" value="F" />
      <cfprocparam cfsqltype="cf_sql_integer" value="4" />
      <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.backupPath#" />
    </cfstoredproc>
    <cfcatch>
      <cflog application="yes" text="Database backup failed #now()# for #ListGetAt(variables.databases, i)#" />
    </cfcatch>
  </cftry>
</cfloop>
<cfcatch>
   <cflog application="yes" text="Database backup failed #now()# outside loop" />
</cfcatch>
</cftry>

Obviously, this is the bare bones of a backup system. You can modify the scripts to perform differential backups to meet your needs, log file backups, and add some error handling to the stored procedures, but this is a good start.

somevariable=1%20and%201=convert(int,(select%20top%201%20username%20from%20adminusers))

or

somevariable=1%20and%201=convert(int,(select%20top%201%20char(97)%2bpassword%20from%20adminusers))

or an attack specific to SQL Server:

somevariable=convert(int,(select top 1 table_name from information_schema.tables))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties)))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties,sysconstraints)))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties,sysconstraints,syssegments)))--sp_password

somevariable=convert(int,(select top 1 table_name from information_schema.tables where table_name not in (dtproperties,sysconstraints,syssegments)))--sp_password

The first problem was an exploit of the user's default error handling page -- if no error handling is in place, the error message might contain the username, password, or other information:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value 'yourpassword' to a column of data type int. <br>The error occurred on line 102.

In the real attack, the user password was shown on the page. The password was prefaced with the letter "A" -- the char(97) in the attack. This is in case the password started with a number. This can be prevented by using <cfqueryparam> or other device specific to your programming language to make sure integer values are passed as integers.

The second problem is that the default web database user has access to tables that should never be accessible to the web. The malicious user was able to obtain table information from information_schema.tables, and work from there, systematically building each time on information that was previously obtained.

The best possible scenario is to turn off all table access to the web and only access data through stored procedures. That is not always possible. At the very minimum, only expose the data necessary for the site, and only allow access to statements that are required for operation of the site. For example, if you have a table called "Payments", and this is only available to admins, create two SQL username/password logins and use one for the publicly accessed site and one for the admin section. Turn off all permissions to the "Payments" table for the web user. Create "SELECT" permissions only on tables that only need to have data displayed.

As a DBA (which you are if you have a web site with a database and you are the person responsible for the database), you need to know how to secure your data. That involves setting up specific database users for specific access. If a web host gives you a dbo user for a specific database, do not under any circumstance use this username for your web site. This user can be used to create web user logins with specific access. MySQL has similar security features. Access users are out of luck.

The other key is never displaying error messages to users. Make sure your error handling page only shows a pretty message to the user with no information in it, like "You've created an error. Go back and try again." Or prettier than that.

And don't use words or letters for username/password combinations. Passwords should be 10 characters or more, and contain letters, numbers, and special characters. Brute force password guessing programs can figure out a password quickly if you use English language words or just letters.

I'm getting these attacks on my site too. It's scary sometimes having a web site, but hopefully there are safety measures in place to keep these parasites out.

• getBrowserSize() -- called in the onload and onresize event to capture the browser size and pass to the server-side page
• getSize() -- gets the size of the browser window
• passFields() -- takes an array of fields (fieldname, value, fieldname, value, etc) and a URL and passes the fields to the URL as querystring variables
• resetSizeTimer() -- creates a timer so that when the browser is resized, only one event is recorded (browser resizing typically fires the onResize event numerous times in succession.)

In addition, we set a global variable to act as a flag for the resize timer. The code is pretty straightforward, and can be placed in the head of any file:

<script>
var size_timer = false;

// Subroutine to get the size of the window
function getSize() {
 var myWidth = 0, myHeight = 0;
 if(typeof(window.innerWidth) == 'number') {
  //Non-IE
  myWidth = window.innerWidth;
  myHeight = window.innerHeight;
 }else if(document.documentElement &&
  (document.documentElement.clientWidth || document.documentElement.clientHeight)) {
  //IE 6+ in 'standards compliant mode'
  myWidth = document.documentElement.clientWidth;
  myHeight = document.documentElement.clientHeight;
 } else if(document.body && (document.body.clientWidth || document.body.clientHeight)) {
  //IE 4 compatible
  myWidth = document.body.clientWidth;
  myHeight = document.body.clientHeight;
 }
 return [myWidth, myHeight];
}

// Pass fields to server given a URL and fields in name/value pairs
function passFields(url,args) {
 url += "?";
 for(var i=0; i<args.length; i=i+2) {
  url += args[i] + "=" + args[i+1] + "&";
 }
 //Set up the AJAX communication
 if (window.XMLHttpRequest) {
  req = new XMLHttpRequest();
 } else if (window.ActiveXObject) {
  req = new ActiveXObject("Microsoft.XMLHTTP");
 }
 try {
  // Pass the URL to the server
  req.open("GET", url, true);
  req.send(null);
 }catch(e) {
 //nothing;
 }
}
function resetSizeTimer() {
 size_timer = false;
}

// Get the size and pass to the server
function getBrowserSize() {
 if(size_timer)return;
 size_timer = true;
 self.setTimeout('resetSizeTimer()',1000);
 var theArray = getSize();
 var url = "getBrowserSize.php";
 var args = new Array();
 args.push("width");
 args.push(theArray[0]);
 args.push("height");
 args.push(theArray[1]);
 args.push("screenwidth");
 args.push(screen.width);
 args.push("screenheight");
 args.push(screen.height);
 args.push("pagename");
 args.push(window.location);
 passFields(url, args);
}

</script>

All you need to do is to add the getBrowserSize() function to the onload and onresize events of the <body> tag:

<body onload="getBrowserSize();" onresize="getBrowserSize();">

Now, when you browse the page, the server records the browser size upon load and upon resize. Typical information would look like this:

The technique is handy and can be used for any other situation where you need to pass client-side information to the server.

Cross-posted at CMXTraneous

First, I create a table in my database to store the information. The following is for SQL Server:

CREATE TABLE BrowserSize (
 browsersize_id int IDENTITY (1, 1) NOT NULL ,
 browsersize_width int NULL ,
 browsersize_height int NULL ,
 browsersize_screenwidth int NULL ,
 browsersize_screenheight int NULL ,
 IP varchar (50) NULL ,
 pagename varchar (255) NULL
)

The following is the equivalent for MySQL:

CREATE TABLE BrowserSize (
 browsersize_id int AUTO_INCREMENT PRIMARY KEY NOT NULL ,
 browsersize_width int NULL ,
 browsersize_height int NULL ,
 browsersize_screenwidth int NULL ,
 browsersize_screenheight int NULL ,
 IP varchar (50) NULL ,
 pagename varchar (255) NULL
);

You could also add a timestamp field, if you want to track times.

Next, I create a server-side page to grab the information and pass it to the database. The information will be passed in the URL. The code is self-explanatory. Basically, we pass width, height, screenwidth, screenheight, and page location in the URL, and insert it into our database table, along with the IP address of the user. The following is for ColdFusion:

<cfparam name="url.width" default=0>
<cfparam name="url.height" default=0>
<cfparam name="url.screenwidth" default=0>
<cfparam name="url.screenheight" default=0>
<cfparam name="url.pagename" default="">
<cfset url.width = val(url.width)>
<cfset url.height = val(url.height)>
<cfset url.screenwidth = val(url.screenwidth)>
<cfset url.screenheight = val(url.screenheight)>
<cfset ip = cgi.REMOTE_ADDR>

<cftry>
<cfquery datasource="yourdsn">
INSERT INTO browserSize
(browsersize_width
, browsersize_height
, browsersize_screenwidth
, browsersize_screenheight
, IP
, pagename)
VALUES
(#url.width#
,#url.height#
,#url.screenwidth#
,#url.screenheight#
,'#ip#'
,'#url.pagename#')
</cfquery>
<cfcatch>
<!--- do nothing --->
</cfcatch>
</cftry>

The following is for PHP:

<?php
$_GET["width"] = isset($_GET["width"]) ? intval($_GET["width"]) : 0;
$_GET["height"] = isset($_GET["height"]) ? intval($_GET["height"]) : 0;
$_GET["screenwidth"] = isset($_GET["screenwidth"]) ? intval($_GET["screenwidth"]) : 0;
$_GET["screenheight"] = isset($_GET["screenheight"]) ? intval($_GET["screenheight"]) : 0;
$pagename = isset($_GET['pagename']) ? $_GET['pagename'] : "";
$ip = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : "";

$conn = mysql_connect("localhost", "username", "password");
$query_rs = sprintf("INSERT INTO browserSize
(browsersize_width
, browsersize_height
, browsersize_screenwidth
, browsersize_screenheight
, IP
, pagename)
VALUES (%s, %s, %s, %s, '%s', '%s')"
,$_GET["width"]
,$_GET["height"]
,$_GET["screenwidth"]
,$_GET["screenheight"]
,$ip
,$pagename);
mysql_select_db("cwtest");
$rs = mysql_query($query_rs);
?>

Both of the pages can be run in the browser to test (saved as getBrowserSize.cfm or getBrowserSize.php respectively). If the values of 0 are inserted in the database, everything is working. I'll post the client-side AJAX code in Part 2.

CREATE FUNCTION fnGetNumberOfWords (
  @stringToSplit varchar(8000),
  @numberOfWords int
)

RETURNS varchar(8000) AS

BEGIN

DECLARE @currentword varchar(8000)
DECLARE @returnstring varchar(8000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@stringToSplit))
Declare @index int

WHILE @wordcount < @numberOfWords AND len(@stringToSplit) > 0
  BEGIN
    Select @index = CHARINDEX(' ', @stringToSplit)
    if @index = 0
      BEGIN
        SELECT @currentword = ltrim(rtrim(@stringToSplit))
        SELECT @wordcount = @numberOfWords
      END
    else
      BEGIN
        IF (len(@stringToSplit) - @index > 0) BEGIN
        SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
        SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
      END
    END
  SELECT @returnstring = @returnstring + ' ' + @currentword
  SELECT @wordcount = @wordcount + 1
END

SET @returnstring = LTRIM(@returnstring)
RETURN @returnstring

END

Call it like this:

SELECT dbo.fnGetNumberOfWords(MyField, 10) FROM mytable

(returns first 10 words from MyField)

The advantage to doing it in the database rather than on the web page, is that you are only returning a small portion of the field to the web page, rather than the entire field. This can speed up the query. A few preliminary tests show that the smaller number of words you return, the quicker the query will execute. In other words, if your query returns a field that can contain up to 8000 characters in it (like a blog entry, for example) and you only need the first 50 words for a summary, the query to return the 50 words will be faster than a query that returns the whole field. Also, your scripted page will execute faster because it is simply displaying the field and not performing any further logic, looping, or parsing on the field.

I talked about user-defined SQL functions in one of my articles at Community MX as well:

Using CSV Strings in SQL Server: Part 2

Note that the function does not work on text or ntext data types. I hope you find it useful.

Cross posted at CMXtraneous

TODCON was a great experience, as usual, and I love the fact that it was in Las Vegas. Holding a conference in Las Vegas pretty much guarantees that no sleep will be had. Between the sessions, the eating and drinking, the socializing, the shows, and the gambling, there is little time for sleep. I picked up a cold while I was there, probably from the smoke, dry air, and lack of sleep, but it was a fun time.

One of the sessions that I particularly enjoyed was by Neil Giarratana on using open source software. He showed a presentation of Mambo, the open source content management system for PHP that was awesome. I may be converting some sites to use the system. As part of Mambo, he showed an amazing online HTML editor that is also open source: TinyMCE. This editor blows away all the other HTML editors I've tried in the past, as it seems to work great with Firefox and Mac browsers. I'll have to do some more experimentation with it.

Dan and Angela from Cartweaver gave away a ton of prizes, including copies of Cartweaver, a year of hosting, and a whole box of books. There were also major giveaways from Webassist, Interakt, Kaosweaver, and of course from Ray West, who puts on the TODCON conferences. Chris Flick was there doing the cartooning thing, of which he is the master.

What would Vegas be without gambling. I did a little, but played only Poker. I am not a big gambler and especially don't like playing against the house, so I avoid games like Roulette and Blackjack, and especially the slot machines. Poker, on the other hand, pits you against other players, and a player with a good knowledge of the game can do quite well. Unfortunately, I didn't. You know what they say -- what happens in Vegas, stays in Vegas. In this case, it was my money that stayed there. We played mostly at the Excalibur, where the conference was, but also tried out the Bellagio, where I have played last time I was there, and saw a few familiar faces in the card room (Johnny Chan, Mel Judah, and Sami Farha, from the WSOP and WPT television shows.) It was fun. I left a few bucks shorter, but it was worth it.

Tuesday night a group of us went to the Zumanity show at the New York, New York casino. What an amazing performance. Some of the performers were tremendous athletes who made it look easy to fly around the arena on a silk scarf or do handstands on one hand on the edge of a plexiglass pool while doing a split. It really had to be seen to be believed. The costumes were great too. ;-) If you saw the show, you know what I mean.

Previously...

More TODCON links:

TODCON Death Match

Some pics...

Christian Cantrell's blog

Kim's blog

Update 5/4/2005: More TODCON links...

Pics from Sheri

Chris Flick on TODCON

John Olson on TODCON

Chris Flick's TODCON strip starts here

Pics from Jim Babbage

Edited 10/1/2007: Fixed link to Mambo.]]> http://www.tom-muck.com/blog/index.cfm?newsid=60 http://www.tom-muck.com/blog/index.cfm?newsid=60 Sat, 30 Apr 2005 23:20:10 GMT I have noticed over the years a very bad practice in using the SQL language: SELECT * FROM mytable. This is one of the worst things you can do for performance of your application. The performance hit is roughly equivalent to the percentage of fields that you don't need on your page. For example, if you only need 2 fields for your web page, but you are doing SELECT * and retrieving 10 fields, the statement will be about 80% slower than if you only selected the 2 fields in the statement:

SELECT * from mytable 100ms

SELECT fname, lname FROM mytable 20ms

Say you have 10 fields in your table. You might think that you are being more efficient by writing this:

SELECT * FROM MyTable

instead of this:

SELECT fname, lname, address, city, state, zip, phone, email FROM mytable.

Since I am using 8 fields in the statement, I am not retrieving data for 2 fields. The timed results might look like this:

1st statement: 100ms

2nd statement 80ms

This is true for the majority of databases and scripting languages across the board (PHP, ColdFusion, ASP, etc). I've even seen pages where a user will write SELECT * FROM mytable and use only one field, where the database table has 20-30 fields in it. This is outrageous, in that the performance penalty is 95-97%. In a busy web application, this can be disastrous.

As a side note, when people send me pages to look at, they are very hard to debug when they use SELECT *. For one thing, I do not have access to your database, so I have to recreate the SQL statement to work with one of my sample databases. It's much easier to do when I can see what fields I need to use.