Re: What could cause an "insert" to choke?

[Randy Clamons]

Your string fields are delimited by a single quote ('), so any data containing that character will break your insert statement. Also, any data containing embedded new line will cause it to fail. Use a backslash to escape the string. I use regular expressions to do the job and escape all data coming from web forms:

sub mysql_escape {
	my $string = @_[0];
	$string =~ s/\\/\\\\/g ; # first escape all backslashes or they disappear
	$string =~ s/\n/\\n/g ; # escape new line chars
	$string =~ s/\r//g ; # escape carriage returns
	$string =~ s/\'/\\\'/g; # escape single quotes
	$string =~ s/\"/\\\"/g; # escape double quotes
	return $string ;
}

Your insert statement will look something like this:

$sth = $dbh->prepare("insert into placements values 
( NULL,
\'@{[ &mysql_escape($idnum)]}\',
\'@{[ &mysql_escape($$lnam)]}\',
\'@{[ &mysql_escape($$fnam)]}\',
\'@{[ &mysql_escape($$queshis)]}\',
\'@{[ &mysql_escape($$anshis)]}\',
\'@{[ &mysql_escape($$random[17])]}\',
\'@{[ &mysql_escape($$reclev)]}\',
\'@{[ &mysql_escape($$startime)]}\',
\'@{[ &mysql_escape($$endtime)]}\',
\'@{[ &mysql_escape($$prevital)]}\',
\'@{[ &mysql_escape($$lastclass)]}\',
\'@{[ &mysql_escape($$otherexposure)]}\',
\'@{[ &mysql_escape($$furtherinfo)]}\',
NULL)");

If you have data that is completely under your program control you may not need to escape all fields. At a minimum you should escape all char, varchar and text fields.