[eluser]megabyte[/eluser]
So I am using the database utilities, and im sure this is nothing to do with it, and more with the data.
But I get this error
ERROR: Unknown Punctuation String
STR: ://
Now has anyone had this happen and know of a way to get around it or fix it?
I had to add a database backup to an existing database with more than 1,000 records.
And when i try to import the backed up .sql file i create, I get that error.
Please help. 
[eluser]TheFuzzy0ne[/eluser]
It would really help if you could give use the context in which that error appears. Please post a bit more of the errornous SQL statement, or the whole thing if possible. If we can replicate the error, it will surely be easier to find a solution. Do you have any procedures in your query?
[eluser]megabyte[/eluser]
Here ya go!
So originally obviously al data was put into the table through a form.
Below you'll find the table structure and an insert that throws the error.
What the problem is, is that I can dump the entire databse succesfully, but when I then go to import it to see if the dump works, I get the error.
The errors are being caused by the existing data, so that is basically what i need to work around.
I'm guessing maybe I need to escape the //
Code: #
# TABLE STRUCTURE FOR: ad_listing
#
DROP TABLE IF EXISTS ad_listing;
CREATE TABLE `ad_listing` (
`AdID` int(11) NOT NULL AUTO_INCREMENT,
`ContactID` int(11) NOT NULL DEFAULT '0',
`FirstName` varchar(50) DEFAULT NULL,
`LastName` varchar(50) DEFAULT NULL,
`Company` varchar(255) DEFAULT NULL,
`Address1` varchar(255) DEFAULT NULL,
`Address2` varchar(255) DEFAULT NULL,
`CityID` int(11) NOT NULL DEFAULT '0',
`Province` varchar(50) DEFAULT NULL,
`PostalCode` varchar(50) DEFAULT NULL,
`Phone1` varchar(50) DEFAULT NULL,
`Phone2` varchar(50) DEFAULT NULL,
`Phone_cell` varchar(50) DEFAULT NULL,
`Phone_tollfree` varchar(50) DEFAULT NULL,
`Fax` varchar(50) DEFAULT NULL,
`Email` varchar(255) DEFAULT NULL,
`Web` varchar(100) DEFAULT NULL,
`Description` text,
`EventTitle` varchar(255) NOT NULL,
`EventDays` varchar(20) NOT NULL,
`EventStartDate` datetime DEFAULT NULL,
`EventEndDate` datetime DEFAULT NULL,
`hrs_monthstart` varchar(25) DEFAULT NULL,
`hrs_monthend` varchar(25) DEFAULT NULL,
`hrs_monthdaystart` int(11) DEFAULT '0',
`hrs_monthdayend` int(11) DEFAULT '0',
`hrs_weekdaystart` varchar(50) DEFAULT NULL,
`hrs_weekdayend` varchar(50) DEFAULT NULL,
`hrs_timestart` varchar(10) DEFAULT NULL,
`hrs_timeend` varchar(10) DEFAULT NULL,
`hrs_other` text,
`hrs_apptonly` tinyint(1) NOT NULL DEFAULT '0',
`regionID` int(11) NOT NULL DEFAULT '0',
`categoryID` int(11) NOT NULL DEFAULT '0',
`DateCreated` datetime DEFAULT NULL,
`DateModified` datetime DEFAULT NULL,
`Active` tinyint(1) NOT NULL DEFAULT '1',
`SampleListing` tinyint(4) NOT NULL DEFAULT '0',
`OrderSequence` int(11) NOT NULL DEFAULT '0',
`StatusID` int(11) NOT NULL DEFAULT '0',
`Weight` decimal(10,0) NOT NULL DEFAULT '0',
PRIMARY KEY (`AdID`),
KEY `ContactID` (`ContactID`),
KEY `FirstName` (`FirstName`),
KEY `LastName` (`LastName`),
KEY `Company` (`Company`),
KEY `CityID` (`CityID`),
KEY `EventStartDate` (`EventStartDate`),
KEY `regionID` (`regionID`),
KEY `categoryID` (`categoryID`),
KEY `Web` (`Web`)
) ENGINE=MyISAM AUTO_INCREMENT=1562 DEFAULT CHARSET=latin1;
INSERT INTO ad_listing (`AdID`, `ContactID`, `FirstName`, `LastName`, `Company`, `Address1`, `Address2`, `CityID`, `Province`, `PostalCode`, `Phone1`, `Phone2`, `Phone_cell`, `Phone_tollfree`, `Fax`, `Email`, `Web`, `Description`, `EventTitle`, `EventDays`, `EventStartDate`, `EventEndDate`, `hrs_monthstart`, `hrs_monthend`, `hrs_monthdaystart`, `hrs_monthdayend`, `hrs_weekdaystart`, `hrs_weekdayend`, `hrs_timestart`, `hrs_timeend`, `hrs_other`, `hrs_apptonly`, `regionID`, `categoryID`, `DateCreated`, `DateModified`, `Active`, `SampleListing`, `OrderSequence`, `StatusID`, `Weight`) VALUES (1064, 7773, NULL, 'Kalnins', 'BC Ferries', '', NULL, 2, 'BC', '', '1-888-BC FERRY (1-888-223-3779) from anywhere in N', '', '', '', NULL, '', 'www.bcferries.com', 'British Columbia Ferry Services Inc., or BC Ferries, is known for <b>safety, spectacular scenery and a proud maritime history. </b>
What began in 1960 as a two vessel, two terminal operation has grown into one of the largest, most sophisticated ferry transportation systems in the world. Our fleet of 36 vessels serves a diverse range of communities - up to 47 ports of call - along the coast of British Columbia, Canada.
As the primary provider of coastal ferry service in the province, BC Ferries recognizes its unique role in the region's economy. From maintaining a constant flow of goods and services, to linking families and friends, to attracting tourists to our many unique destinations, our company is a key participant in the development
BC Ferries ~ Life on the Coast
Visit our website for Schedules & Route Information. [www.bcferries.com](http://www.bcferries.com)', '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2, 20, '2007-06-13 17:17:39', '2007-06-13 17:20:48', 1, 0, 10, 0, '0');
[eluser]TheFuzzy0ne[/eluser]
I think the problem is the unescaped apostrophe in the word "region's". I changed the quotes from single quotes two double quotes. I'd be interested to know if this inserts correctly.
Code: INSERT INTO ad_listing (`AdID`, `ContactID`, `FirstName`, `LastName`, `Company`, `Address1`, `Address2`, `CityID`, `Province`, `PostalCode`, `Phone1`, `Phone2`, `Phone_cell`, `Phone_tollfree`, `Fax`, `Email`, `Web`, `Description`, `EventTitle`, `EventDays`, `EventStartDate`, `EventEndDate`, `hrs_monthstart`, `hrs_monthend`, `hrs_monthdaystart`, `hrs_monthdayend`, `hrs_weekdaystart`, `hrs_weekdayend`, `hrs_timestart`, `hrs_timeend`, `hrs_other`, `hrs_apptonly`, `regionID`, `categoryID`, `DateCreated`, `DateModified`, `Active`, `SampleListing`, `OrderSequence`, `StatusID`, `Weight`) VALUES
(1064, 7773, NULL, 'Kalnins', 'BC Ferries', '', NULL, 2, 'BC', '', '1-888-BC FERRY (1-888-223-3779) from anywhere in N', '', '', '', NULL, '', 'www.bcferries.com', "British Columbia Ferry Services Inc., or BC Ferries, is known for <b>safety, spectacular scenery and a proud maritime history. </b>
What began in 1960 as a two vessel, two terminal operation has grown into one of the largest, most sophisticated ferry transportation systems in the world. Our fleet of 36 vessels serves a diverse range of communities - up to 47 ports of call - along the coast of British Columbia, Canada.
As the primary provider of coastal ferry service in the province, BC Ferries recognizes its unique role in the region's economy. From maintaining a constant flow of goods and services, to linking families and friends, to attracting tourists to our many unique destinations, our company is a key participant in the development
BC Ferries ~ Life on the Coast
Visit our website for Schedules & Route Information. [www.bcferries.com](http://www.bcferries.com)", '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2, 20, '2007-06-13 17:17:39', '2007-06-13 17:20:48', 1, 0, 10, 0, '0');
[eluser]megabyte[/eluser]
I figured ti out, there's a single quote not being escaped.
region's
Now i just have to figure out why the dump utility isnt escaping it. the field type is text.
[eluser]megabyte[/eluser]
OK, So inthe _backup() where they check to see if its a string and whether or not to escape it, theyt use this function to escape the data
Code: function escape_str($str)
{
if (get_magic_quotes_gpc())
{
return $str;
}
if (function_exists('mysql_real_escape_string'))
{
return mysql_real_escape_string($str, $this->conn_id);
}
elseif (function_exists('mysql_escape_string'))
{
return mysql_escape_string($str);
}
else
{
return addslashes($str);
}
}
So whats happening is get_magic_quotes_gpc() is returning a 1 for me, so the string is not being escaped.
Now this is the way I figure to explain this.
The escape string function is great for creating sql statements for inserts if magic quotes is turned on, and the sql statements are being inserted into the database on the same server that the sql is being created on, but as far as portability with a dump file, what if you want to move the database and magic quotes is turned off on the new server.
I think this is what is happening, because I'm dumping it from one server, then testing it on another using phpmyadmin.
Does this sound like I'm making sense?
if so then for backups we should remove
Code: if (get_magic_quotes_gpc())
{
return $str;
}
//from the function below.
function escape_str($str)
{
if (get_magic_quotes_gpc())
{
return $str;
}
if (function_exists('mysql_real_escape_string'))
{
return mysql_real_escape_string($str, $this->conn_id);
}
elseif (function_exists('mysql_escape_string'))
{
return mysql_escape_string($str);
}
else
{
return addslashes($str);
}
}
[eluser]Rambo Frank[/eluser]
From my understanding a field being text does not mean it will escape the insert string. It will more than likely cause problems. An easy fix for it is to just use the mysql_real_escape_string(). It prevents problems such as this as well as sql injection.
|
