...

View Full Version : uploading HTML to database - getting error



Phil Jackson
02-07-2010, 09:46 AM
Hi, I have written a script to do numerous amounts of task which at the end of, is to get a bit of HTML code and upload to database. This loops through for each file/page.

Problem being when getting to the 4th page i get an error. Below is part of my code.

.

..
...
my $fileContents;
if( $md5Con =~ m/\.php$/g ) {
my $ftp = Net::FTP->new($DB_ftpserver, Debug => 0) or die "Cannot connect to some.host.name: $@";
$ftp->login($DB_ftpuser, $DB_ftppass) or die "Cannot login ", $ftp->message;
$ftp->get("/" . $root . $webpage, "c:/perlscripts/" . md5_hex($md5Con) . "-code.php") or die $ftp->message;
my $file = "c:/perlscripts/" . md5_hex($md5Con) . "-code.php";
{
local( $/ ); # undefine the record seperator
open FILE, "<", $file or die "Cannot open:$!\n";
my $fileContents = <FILE>;

my $bodyContents;
my $headContents;

my @contentsArray = split( /<\/head>/is, $fileContents, 2);

if( scalar @contentsArray == 2 ){
$bodyContents = $dbh->quote(trim($contentsArray[1]));
$headContents = $dbh->quote(trim($contentsArray[0]) . "</head>");

print $headContents . "\n";

$dbh->do($createBodyTable) or die " error: Couldn't create body table: " . DBI->errstr;
$dbh->do($createHeadTable) or die " error: Couldn't create header table: " . DBI->errstr;
$dbh->do("INSERT INTO $headerTable ( headData, headDataOutput ) VALUES ( $headContents, $headContents )") or die " error: Couldn't connect to database: " . DBI->errstr;
$dbh->do("INSERT INTO $bodyTable ( bodyData, bodyDataOutput ) VALUES ( $bodyContents, $bodyContents )") or die " error: Couldn't connect to database: " . DBI->errstr;
$dbh->do("INSERT INTO page_names (linkFromRoot, linkTrue, page_name, table_name, navigation, location) VALUES ( $linkFromRoot, $linkTrue, $page_name, $table_name, $navigation, $location )") or die " error: Couldn't connect to database: " . DBI->errstr;

unlink("c:/perlscripts/" . md5_hex($md5Con) . "-code.php");
}else{
print "<span class=\"red\">" . $md5Con . " cannot be used by our CMS, invalid data.</span><br />\n";
}
}
$ftp->quit;
}
...
..
.



I have added print $headContents to be able to show you what code is going in when the error is triggered.



<span class="green">http://www.themobilemakeover.co.uk/index.php</span><br />

'<?php session_start(); ?>\n<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\n<html xmlns=\"h
ttp://www.w3.org/1999/xhtml\">\n<head>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n<meta name=\"description\" content=\"The Mo
bile Makeover offers a wide range of services and treatments all from the luxuary of your own home!\" />\n<meta name=\"keywords\" content=\"Mobile Makeover, B
eauty Therapist, Beautician, Therapist, Mobile Therapist, Mansfield, Nottinghamshire\" />\n<meta name=\"language\" content=\"en\" />\n<meta name=\"author\" co
ntent=\"ACT Web Designs\" />\n<meta name=\"copyright\" content=\"The Mobile Makeover\" />\n<meta name=\"publisher\" content=\"ACT Web Designs\" />\n<meta name
=\"country\" content=\"United Kingdom\" />\n<meta name=\"city\" content=\"Mansfield, Nottinghamshire\" />\n<meta http-equiv=\"X-UA-Compatible\" content=\"IE=8
\" /> \n<meta name=\"verify-v1\" content=\"4lZIaMmjLMq+UA8nkPYB9RjF5RreNwF3Mzurm9JYvQM=\" />\n<title>The Mobile Makeover - Mobile Beautician</title>\n<link hr
ef=\"stylesheets/main.css\" rel=\"stylesheet\" type=\"text/css\" />\n<link href=\"stylesheets/cssReset.css\" rel=\"stylesheet\" type=\"text/css\" />\n<script
type=\"text/javascript\" src=\"js/jquery-1.3.1.js\"></script>\n<script type=\"text/javascript\" src=\"js/jquery.bgpos.js\"></script>\n<script type=\"text/java
script\" src=\"js/jquery.timers.js\"></script>\n<script type=\"text/javascript\" src=\"js/makeover.scroll.js\"></script>\n<script type=\"text/javascript\">\nv
ar gaJsHost = ((\"https:\" == document.location.protocol) ? \"https://ssl.\" : \"http://www.\");\ndocument.write(unescape(\"%3Cscript src=\'\" + gaJsHost + \"
google-analytics.com/ga.js\' type=\'text/javascript\'%3E%3C/script%3E\"));\n</script>\n<script type=\"text/javascript\">\ntry {\nvar pageTracker = _gat._getTr
acker(\"UA-8193659-6\");\npageTracker._trackPageview();\n} catch(err) {}</script></head>'


<span class="green">http://www.themobilemakeover.co.uk/about-us-the-mobile-makeover.php</span><br />


'<?php session_start(); ?>\n<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\n<html xmlns=\"h
ttp://www.w3.org/1999/xhtml\">\n<head>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n<meta name=\"description\" content=\">My na
me is Kelly Knight, I am a highly Qualifed Mobile Beauty Therapist. I have trained in NVQ Level 2 and NVQ level 3 in Beauty Therapy with many additional cours
es and training...\" />\n<meta name=\"keywords\" content=\"Mobile Makeover, About Us, Beauty Therapist, Mobile Therapist, Mansfield, Nottinghamshire\" />\n<me
ta name=\"language\" content=\"en\" />\n<meta name=\"author\" content=\"ACT Web Designs\" />\n<meta name=\"copyright\" content=\"The Mobile Makeover\" />\n<me
ta name=\"publisher\" content=\"ACT Web Designs\" />\n<meta name=\"country\" content=\"United Kingdom\" />\n<meta name=\"city\" content=\"Mansfield, Nottingha
mshire\" />\n<meta http-equiv=\"X-UA-Compatible\" content=\"IE=8\" /> \n<title>The Mobile Makeover - About Us</title>\n<link href=\"stylesheets/main.css\" rel
=\"stylesheet\" type=\"text/css\" />\n<link href=\"stylesheets/cssReset.css\" rel=\"stylesheet\" type=\"text/css\" />\n<script type=\"text/javascript\" src=\"
js/jquery-1.3.1.js\"></script>\n<script type=\"text/javascript\" src=\"js/jquery.bgpos.js\"></script>\n<script type=\"text/javascript\" src=\"js/jquery.timers
.js\"></script>\n<script type=\"text/javascript\" src=\"js/makeover.scroll.js\"></script>\n<script type=\"text/javascript\">\nvar gaJsHost = ((\"https:\" == d
ocument.location.protocol) ? \"https://ssl.\" : \"http://www.\");\ndocument.write(unescape(\"%3Cscript src=\'\" + gaJsHost + \"google-analytics.com/ga.js\' ty
pe=\'text/javascript\'%3E%3C/script%3E\"));\n</script>\n<script type=\"text/javascript\">\ntry {\nvar pageTracker = _gat._getTracker(\"UA-8193659-6\");\npageT
racker._trackPageview();\n} catch(err) {}</script></head>'


<span class="green">http://www.themobilemakeover.co.uk/beauty-products-used.php</span><br />


'<?php session_start(); ?>\n<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\n<html xmlns=\"h
ttp://www.w3.org/1999/xhtml\">\n<head>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n<meta name=\"description\" content=\"We use
a wide range of the finest products on the market today. Below is a short brief of just a few of the names which we believe give you the best results - we do
not compromise when it comes to quality.\" />\n<meta name=\"keywords\" content=\"Mobile Makeover, Beauty Therapist, Beauty Products, Jessica, Calgel, Eve Tay
lor, Tantrick, Therapist, Mansfield, Nottinghamshire\" />\n<meta name=\"language\" content=\"en\" />\n<meta name=\"author\" content=\"ACT Web Designs\" />\n<m
eta name=\"copyright\" content=\"The Mobile Makeover\" />\n<meta name=\"publisher\" content=\"ACT Web Designs\" />\n<meta name=\"country\" content=\"United Ki
ngdom\" />\n<meta name=\"city\" content=\"Mansfield, Nottinghamshire\" />\n<meta http-equiv=\"X-UA-Compatible\" content=\"IE=8\" /> \n<title>The Mobile Makeov
er - Beauty Products Used</title>\n<link href=\"stylesheets/main.css\" rel=\"stylesheet\" type=\"text/css\" />\n<link href=\"stylesheets/cssReset.css\" rel=\"
stylesheet\" type=\"text/css\" />\n<!--[if lte IE 6]><link href=\"stylesheets/mainie6.css\" rel=\"stylesheet\" type=\"text/css\" /><![endif]-->\n<script type=
\"text/javascript\" src=\"js/jquery-1.3.1.js\"></script>\n<script type=\"text/javascript\" src=\"js/jquery.bgpos.js\"></script>\n<script type=\"text/javascrip
t\" src=\"js/jquery.timers.js\"></script>\n<script type=\"text/javascript\" src=\"js/makeover.scroll.js\"></script>\n<script type=\"text/javascript\">\nvar ga
JsHost = ((\"https:\" == document.location.protocol) ? \"https://ssl.\" : \"http://www.\");\ndocument.write(unescape(\"%3Cscript src=\'\" + gaJsHost + \"googl
e-analytics.com/ga.js\' type=\'text/javascript\'%3E%3C/script%3E\"));\n</script>\n<script type=\"text/javascript\">\ntry {\nvar pageTracker = _gat._getTracker
(\"UA-8193659-6\");\npageTracker._trackPageview();\n} catch(err) {}</script></head>'


<span class="green">http://www.themobilemakeover.co.uk/beauty-treatments.php</span><br />


'<?php session_start(); ?>\n<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\n<html xmlns=\"h
ttp://www.w3.org/1999/xhtml\">\n<head>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n<meta name=\"description\" content=\"The Mo
bile Makeover offers a wide range of treatments all from the luxuary of your own home!\" />\n<meta name=\"keywords\" content=\"Mobile Makeover, Beauty Treatme
nts, Beautician, Facials, Pedicures, Hopi Ear Candles, Eye Treatments, Mansfield, Nottinghamshire\" />\n<meta name=\"language\" content=\"en\" />\n<meta name=
\"author\" content=\"ACT Web Designs\" />\n<meta name=\"copyright\" content=\"The Mobile Makeover\" />\n<meta name=\"publisher\" content=\"ACT Web Designs\" /
>\n<meta name=\"country\" content=\"United Kingdom\" />\n<meta name=\"city\" content=\"Mansfield, Nottinghamshire\" />\n<meta http-equiv=\"X-UA-Compatible\" c
ontent=\"IE=8\" /> \n<title>The Mobile Makeover - Beauty Treatments</title>\n<link href=\"stylesheets/main.css\" rel=\"stylesheet\" type=\"text/css\" />\n<lin
k href=\"stylesheets/cssReset.css\" rel=\"stylesheet\" type=\"text/css\" />\n<script type=\"text/javascript\" src=\"js/jquery-1.3.1.js\"></script>\n<script ty
pe=\"text/javascript\" src=\"js/jquery.bgpos.js\"></script>\n<script type=\"text/javascript\" src=\"js/jquery.timers.js\"></script>\n<script type=\"text/javas
cript\" src=\"js/makeover.scroll.js\"></script>\n<script type=\"text/javascript\">\nvar gaJsHost = ((\"https:\" == document.location.protocol) ? \"https://ssl
.\" : \"http://www.\");\ndocument.write(unescape(\"%3Cscript src=\'\" + gaJsHost + \"google-analytics.com/ga.js\' type=\'text/javascript\'%3E%3C/script%3E\"))
;\n</script>\n<script type=\"text/javascript\">\ntry {\nvar pageTracker = _gat._getTracker(\"UA-8193659-6\");\npageTracker._trackPageview();\n} catch(err) {}<
/script></head>'


DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use nea
r '22e64cef7d70fa952ce7444f158e2c4e_header ( headData, headDataOutput ) VALUES ( '<' at line 1 at C:\perlscripts\cgitest.pl line 360, <FILE> chunk 4.


error: Couldn't connect to database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synta
x to use near '22e64cef7d70fa952ce7444f158e2c4e_header ( headData, headDataOutput ) VALUES ( '<' at line 1 at C:\perlscripts\cgitest.pl line 360, <FILE> chunk
4.



The span tag is just printed to show what page is currently being processed, it's the block of code underneath that is being uploaded.

Any help is much appreciated.

FishMonger
02-07-2010, 03:57 PM
Off hand, I'd say that it's having a problem with either the table name or the '<' in $headContents.

See if this change (backticks around the table) solves the problem. (I reformatted the line for readability)

$dbh->do("INSERT INTO `$headerTable` ( headData, headDataOutput )
VALUES ( $headContents, $headContents )")
or die " error: Couldn't connect to database: " . DBI->errstr;

FishMonger
02-07-2010, 04:05 PM
Why are duplicating the data across 2 db columns?

Phil Jackson
02-07-2010, 04:27 PM
I am doing that just to get this to work. the first col will have that data but the second will contain what is outputted to the browser. I will try ` thanks.

Phil Jackson
02-07-2010, 04:34 PM
Fish, this is my code. Getting different errors when trying to use `.



my $headDataUpload = $dbh->prepare("INSERT INTO ? ( headData, headDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;
my $bodyDataUpload = $dbh->prepare("INSERT INTO ? ( bodyData, bodyDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;
my $insertData = $dbh->prepare("INSERT INTO page_names (linkFromRoot, linkTrue, page_name, table_name, navigation, location) VALUES ( ?, ?, ?, ?, ?, ? )") or die " error: Couldn't connect to database: " . DBI->errstr;


foreach( @mainPagesArray ) {
my $webpage = &extention(trim($_));
if( trim($webpage) ne trim($domain) ){
my $webpageQuote = $dbh->quote("http://www." . $webpage);
my $sth = $dbh->prepare("SELECT * FROM page_names WHERE linkTrue = $webpageQuote ") or die "Could not select from table" . $DBI::errstr;
$sth->execute();
if( $sth->rows == 0 ) {
#output display pages found
print "<span class=\"green\">http://www." . $webpage . "</span><br />\n";
my $md5Con = "http://www." . $webpage;
my $linkTrue = "http://www." . $webpage;
$webpage =~ s/^$domain//g;
my $linkFromRoot = $root . $webpage;

my $getTitleContents = get($md5Con);
my $stringPageName;
if( $getTitleContents =~ m/<title>([^<]*)<\/title>/g ) {
my @titles = ($1);
my $counter = 2;
while(@titles){
my $currentTitle = shift @titles;
my $titleQuoted = $dbh->quote($currentTitle);
my $sth = $dbh->prepare("SELECT * FROM page_names WHERE page_name = $titleQuoted ") or die "Could not select from table" . $DBI::errstr;
$sth->execute();
if( $sth->rows == 0 ) {
$stringPageName = $currentTitle;
last;
}else{
my $newTitle = $currentTitle . "(" . $counter . ")";
push(@titles, $newTitle);
$counter ++;
}
}
}else{
my @titles = ('Untitled');
my $counter = 2;
while(@titles){
my $currentTitle = shift @titles;
my $titleQuoted = $dbh->quote($currentTitle);
my $sth = $dbh->prepare("SELECT * FROM page_names WHERE page_name = $titleQuoted ") or die "Could not select from table" . $DBI::errstr;
$sth->execute();
if( $sth->rows == 0 ) {
$stringPageName = $currentTitle;
last;
}else{
my $newTitle = $currentTitle . "(" . $counter . ")";
push(@titles, $newTitle);
$counter ++;
}
}
}

my $page_name = $stringPageName;
my $table_name = md5_hex($md5Con);
my $navigation = "1";
my $location = "1";

my $bodyTable = md5_hex($md5Con) . "_body";
my $headerTable = md5_hex($md5Con) . "_header";

my $createBodyTable = "CREATE TABLE IF NOT EXISTS `$bodyTable` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`bodyData` TEXT NOT NULL,
`bodyDataOutput` TEXT NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;";

my $createHeadTable = "CREATE TABLE IF NOT EXISTS `$headerTable` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`headData` TEXT NOT NULL,
`headDataOutput` TEXT NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;";

my $fileContents;
if( $md5Con =~ m/\.php$/g ) {
my $ftp = Net::FTP->new($DB_ftpserver, Debug => 0) or die "Cannot connect to some.host.name: $@";
$ftp->login($DB_ftpuser, $DB_ftppass) or die "Cannot login ", $ftp->message;
$ftp->get("/" . $root . $webpage, "c:/perlscripts/" . md5_hex($md5Con) . "-code.php") or die $ftp->message;
my $file = "c:/perlscripts/" . md5_hex($md5Con) . "-code.php";
{
local( $/ ); # undefine the record seperator
open FILE, "<", $file or die "Cannot open:$!\n";
my $fileContents = <FILE>;
my $bodyContents;
my $headContents;

my @contentsArray = split( /<\/head>/is, $fileContents, 2);

#print $contentsArray[1];
if( scalar @contentsArray == 2 ){
$bodyContents = trim($contentsArray[1]);
$headContents = trim($contentsArray[0]) . "</head>";

#print $headContents . "\n";

$dbh->do($createBodyTable) or die " error: Couldn't create body table: " . DBI->errstr;
$dbh->do($createHeadTable) or die " error: Couldn't create header table: " . DBI->errstr;

$headDataUpload->execute(`$headerTable`, $headContents, $headContents);
$bodyDataUpload->execute(`$bodyTable`, $bodyContents, $bodyContents);

# the above is what is causing the error

#$headDataUpload->execute($headerTable, 'foo', 'bar');
#$bodyDataUpload->execute($bodyTable, 'bar', 'foo');

# The above still causes error

#$headDataUpload->execute('6959bbd13fdb4df586a5b9d08aae1153_header', 'foo', 'bar');
#$bodyDataUpload->execute('6959bbd13fdb4df586a5b9d08aae1153_header', 'bar', 'foo');

$insertData->execute($linkFromRoot, $linkTrue, $page_name, $table_name, $navigation, $location);

#unlink("c:/perlscripts/" . md5_hex($md5Con) . "-code.php");

}else{
print "<span class=\"red\">" . $md5Con . " cannot be used by our CMS, invalid data.</span><br />\n";
}
}
$ftp->quit;
}elsif( $md5Con =~ m/(?:(?:\.asp)|(?:\.aspx))$/g ){

}

}
}
}
}else{
print "<span class=\"red\"> error: No pages where found. This CMS is designed for pre-existing sites. Please contact support for more information.</span><br />\n";
}


I tried both



my $headDataUpload = $dbh->prepare("INSERT INTO `?` ( headData, headDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;
my $bodyDataUpload = $dbh->prepare("INSERT INTO `?` ( bodyData, bodyDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;


and



$headDataUpload->execute(`$headerTable`, $headContents, $headContents);
$bodyDataUpload->execute(`$bodyTable`, $bodyContents, $bodyContents);


but get error



'a4cbeef10b3c6d44ca30d96370619eef_body' is not recognized as an internal or external command,
operable program or batch file.
DBD::mysql::st execute failed: called with 2 bind variables when 3 are needed at C:\perlscripts\cgitest.pl line 369, <FILE> chunk 14.

Phil Jackson
02-07-2010, 04:46 PM
I've got it to work but i dont know if it is the correct way.



if( scalar @contentsArray == 2 ){

$bodyContents = trim($contentsArray[1]);
$headContents = trim($contentsArray[0]) . "</head>";

$dbh->do($createBodyTable) or die " error: Couldn't create body table: " . DBI->errstr;
$dbh->do($createHeadTable) or die " error: Couldn't create header table: " . DBI->errstr;

my $headDataUpload = $dbh->prepare("INSERT INTO `$headerTable` ( headData, headDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;
my $bodyDataUpload = $dbh->prepare("INSERT INTO `$bodyTable` ( bodyData, bodyDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;

$headDataUpload->execute($headContents, $headContents);
$bodyDataUpload->execute($bodyContents, $bodyContents);

$insertData->execute($linkFromRoot, $linkTrue, $page_name, $table_name, $navigation, $location);

}else{
print "<span class=\"red\">" . $md5Con . " cannot be used by our CMS, invalid data.</span><br />\n";
}


cheers fishmonger.

FishMonger
02-07-2010, 05:29 PM
The backticks outside of a quoted string have special meaning. They are used to fork a new process that runs an external command and returns the output of that command. Obviously that's not what you want here.

FishMonger
02-07-2010, 05:38 PM
I've got it to work but i dont know if it is the correct way.



if( scalar @contentsArray == 2 ){

$bodyContents = trim($contentsArray[1]);
$headContents = trim($contentsArray[0]) . "</head>";

$dbh->do($createBodyTable) or die " error: Couldn't create body table: " . DBI->errstr;
$dbh->do($createHeadTable) or die " error: Couldn't create header table: " . DBI->errstr;

my $headDataUpload = $dbh->prepare("INSERT INTO `$headerTable` ( headData, headDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;
my $bodyDataUpload = $dbh->prepare("INSERT INTO `$bodyTable` ( bodyData, bodyDataOutput ) VALUES ( ?, ? )") or die " error: Couldn't prepare : " . DBI->errstr;

$headDataUpload->execute($headContents, $headContents);
$bodyDataUpload->execute($bodyContents, $bodyContents);

$insertData->execute($linkFromRoot, $linkTrue, $page_name, $table_name, $navigation, $location);

}else{
print "<span class=\"red\">" . $md5Con . " cannot be used by our CMS, invalid data.</span><br />\n";
}


cheers fishmonger.

Actually, IMO that is the best way of doing it. The backticks around the table name may or may not be required in this case, but they don't hurt.

You still need to work on your code formatting. In particular, the line lengths, indentation, methods of quoting to reduce and/or avoid unnecessary escaping, and unnecessary use of concatenation.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum