I got hold of a Classifieds Script, and when I go to submit a Classified Ad I get the error below. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's', images = '', Price = '', DatePosted = '1183133314', ' at line 7 The funny thing is it only happens when I use the symbol ' So if I write an ad like "Selling a PC for $100" it is ok... But if I put "Selling 2 PC's" then I get the error, so it is the ' symbol that is causing this I presume. Anybody have any suggestions, as I know bugger all about databases. Below is what I think is the database code for the section above: -- -- Table structure for table `class_catalog` -- CREATE TABLE `class_catalog` ( `ProductID` int(10) NOT NULL auto_increment, `MemberID` int(10) NOT NULL default '0', `ProductType` varchar(10) NOT NULL default '', `url` varchar(255) NOT NULL default '', `CategoryID` int(10) NOT NULL default '0', `ProductName` varchar(255) NOT NULL default '', `Description` varchar(2000) NOT NULL default '', `images` text NOT NULL, `Price` float(10,2) NOT NULL default '0.00', `views` int(10) NOT NULL default '0', `DatePosted` int(10) NOT NULL default '0', `DateExp` int(10) NOT NULL default '0', `FeaturedStatus` int(1) NOT NULL default '0', PRIMARY KEY (`ProductID`), FULLTEXT KEY `images` (`images`), FULLTEXT KEY `images_2` (`images`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `class_catalog` -- Maybe you see something I don't
nothing funny, it's pretty obvious why : you're breaking up the query. Have a look at the following query: INSERT INTO mytable VALUES ( 'hello, how's everything?' ) Code (markup): your sql server thinks you stopped the string after the w, because the opened single quote (right after the '(' ) is closed. To fix it, you have to escape the single quote by adding a backslash before the single quote: INSERT INTO mytable VALUES ( 'hello how\'s everything?' ) Code (markup):
Ok thats great that you know this But could you possibly use the code I included in your example, I kinda get what you are saying, but I know nothing.................... about database code, html, php etc etc If you could that would be great.....
if it's php, you can use the mysql_real_escape_function to escape the sensitive characters: $query = sprintf ( "INSERT INTO mytable VALUES ( '%s' )", mysql_real_escape_string ( "hello how's everything" ) ); echo $query; PHP: will output the exact same query as the second one i posted above
Ok totally lost Where would I put the first bit of code you posted into the database? or Where would I put the second bit of code you posted into the .php file? Sorry these questions are soooooooooo stupid, really thankful for your patience
the php code i posted is an example, you will have to post your php code before i can help you with that. there must be some calls to the mysql_query function in your script. You're passing the query as parameter into that function. The php code i posted above is the query you should use all the time, to make sure the query can't break up
Ok yeah, when I write pc\'s it comes out ok....... But I can hardly expect people posting on the site to do this
This is all of the database tables I exported, took out passwords, database etc I bolded the section where the problem is.... -- phpMyAdmin SQL Dump -- version 2.10.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jun 29, 2007 at 07:43 AM -- Server version: 5.0.24 -- PHP Version: 5.2.2 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `xxxxxxxxxx` -- -- -- -- Table structure for table `class_admin` -- CREATE TABLE `class_admin` ( `AdminID` int(10) NOT NULL auto_increment, `username` varchar(50) NOT NULL default '', `password` varchar(50) NOT NULL default '', PRIMARY KEY (`AdminID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- -- Dumping data for table `class_admin` -- INSERT INTO `class_admin` (`AdminID`, `username`, `password`) VALUES (1, 'XXXXX', 'XXXXXXX'); -- -- -- Table structure for table `class_banners` -- CREATE TABLE `class_banners` ( `BannerID` int(10) NOT NULL auto_increment, `BannerFile` text NOT NULL, `BannerAlt` varchar(255) NOT NULL default '', `BannerURL` text NOT NULL, PRIMARY KEY (`BannerID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; -- -- -- Table structure for table `class_catalog` -- CREATE TABLE `class_catalog` ( `ProductID` int(10) NOT NULL auto_increment, `MemberID` int(10) NOT NULL default '0', `ProductType` varchar(10) NOT NULL default '', `url` varchar(255) NOT NULL default '', `CategoryID` int(10) NOT NULL default '0', `ProductName` varchar(255) NOT NULL default '', `Description` varchar(2000) NOT NULL default '', `images` text NOT NULL, `Price` float(10,2) NOT NULL default '0.00', `views` int(10) NOT NULL default '0', `DatePosted` int(10) NOT NULL default '0', `DateExp` int(10) NOT NULL default '0', `FeaturedStatus` int(1) NOT NULL default '0', PRIMARY KEY (`ProductID`), FULLTEXT KEY `images` (`images`), FULLTEXT KEY `images_2` (`images`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `class_catalog` -- -- -- -- Table structure for table `class_categories` -- CREATE TABLE `class_categories` ( `CategoryID` int(10) NOT NULL auto_increment, `CategoryName` varchar(255) NOT NULL default '', PRIMARY KEY (`CategoryID`), UNIQUE KEY `CategoryName` (`CategoryName`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ; -- -- Dumping data for table `class_categories` -- INSERT INTO `class_categories` (`CategoryID`, `CategoryName`) VALUES (2, 'Art / Hobbies'), (3, 'Auction / Classifields'), (4, 'Auto'), (5, 'Beauty / Women'), (6, 'Broadcast'), (7, 'Business'), (8, 'Career / Jobs'), (9, 'Chat / Mail'), (10, 'Children / Teens'), (11, 'Commerce / Retail'), (12, 'Computers / Tech'), (13, 'Education / Reference'), (14, 'Entertainment'), (15, 'Family'), (16, 'Finance'), (17, 'Gambling / Prizes'), (18, 'Hardware'), (19, 'Health / Medecine'), (20, 'Home / Garden'), (21, 'Humor'), (22, 'Industries'), (23, 'International'), (24, 'Internet'), (25, 'Legal'), (26, 'Music / MP3'), (27, 'News / World'), (28, 'Personals / Love'), (29, 'Portals / Search'), (30, 'Real Estate'), (31, 'Regional'), (32, 'Religion / Spirit'), (33, 'Sex / Adult'), (34, 'Sports'), (35, 'Travel'); -- -- -- Table structure for table `class_members` -- CREATE TABLE `class_members` ( `MemberID` int(10) NOT NULL auto_increment, `username` varchar(50) NOT NULL default '', `password` varchar(50) NOT NULL default '', `FirstName` varchar(100) NOT NULL default '', `LastName` varchar(100) NOT NULL default '', `Address` varchar(255) NOT NULL default '', `City` varchar(255) NOT NULL default '', `State` varchar(255) NOT NULL default '', `ZipCode` varchar(25) NOT NULL default '', `Country` varchar(255) NOT NULL default '', `AlternatePhone` varchar(50) NOT NULL default '', `Fax` varchar(50) NOT NULL default '', `email` varchar(150) NOT NULL default '', `Phone` varchar(50) NOT NULL default '', `StandardAds` int(5) NOT NULL default '0', `FeaturedAds` int(5) NOT NULL default '0', `ExpDate` int(10) NOT NULL default '0', `notified` char(1) NOT NULL default 'n', `AccountStatus` varchar(10) NOT NULL default 'inactive', `RegDate` int(10) NOT NULL default '0', `news` char(1) NOT NULL default 'y', `format` varchar(10) NOT NULL default 'plain', PRIMARY KEY (`MemberID`), UNIQUE KEY `username` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- -- Dumping data for table `class_members` -- -- -- -- Table structure for table `class_prices` -- CREATE TABLE `class_prices` ( `PriceID` int(10) NOT NULL auto_increment, `ads` int(5) NOT NULL default '0', `days` int(5) NOT NULL default '0', `price` float(10,2) NOT NULL default '0.00', `PriorityLevel` int(1) NOT NULL default '0', PRIMARY KEY (`PriceID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; -- -- Dumping data for table `class_prices` -- INSERT INTO `class_prices` (`PriceID`, `ads`, `days`, `price`, `PriorityLevel`) VALUES (1, 1, 7, 5.60, 1), (2, 1, 7, 2.80, 0), (3, 1, 14, 4.20, 0), (4, 1, 30, 6.00, 0), (5, 1, 30, 12.00, 1), (6, 1, 14, 8.40, 1); -- -- -- Table structure for table `class_settings` -- CREATE TABLE `class_settings` ( `id` int(1) NOT NULL default '0', `SiteTitle` text NOT NULL, `SiteKeywords` text NOT NULL, `SiteDesc` text NOT NULL, `ContactEmail` varchar(150) NOT NULL default '', `PayPalEmail` varchar(150) NOT NULL default '', `SellerID` varchar(10) NOT NULL default '', `terms` text NOT NULL, `testimonials` text NOT NULL, `message` text NOT NULL, `sp_vendor_email` varchar(255) NOT NULL default '', `sp_payee_email` varchar(255) NOT NULL default '', `sp_secret_code` varchar(255) NOT NULL default '', `banner_price` varchar(10) NOT NULL default '', `news_price` varchar(10) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
There's no problem with your database, but with your script (which you sent me by email). I'll post your script here: $q1 = "insert into class_catalog set MemberID = '$_SESSION[MemberID]', ProductType = '$_POST[ProductType]', url = '$_POST[url]', CategoryID = '$_POST[CategoryID]', ProductName = '$_POST[ProductName]', Description = '$_POST[Description]', images = '$ImageStr', Price = '$_POST[Price]', DatePosted = '$t', DateExp = '$_SESSION[AccountExpDate]', FeaturedStatus = '$_POST[sp]' "; PHP: this is the query you use to insert the new data into the table. Now if i enter a string with a single quote in there it will break up the query, messing up your script. So what you need to do is make sure it doesn't break up, by using mysql_real_escape_string on each entered value (the $_POST variables), like this: $q1 = "insert into class_catalog set MemberID = '" . mysql_real_escape_string ( $_SESSION['MemberID'] ) . "', ProductType = '" . mysql_real_escape_string ($_POST['ProductType'] ) . "', url = '" . mysql_real_escape_string ($_POST['url'] ) . "', CategoryID = '" . mysql_real_escape_string ($_POST['CategoryID'] ) . "', ProductName = '" . mysql_real_escape_string ($_POST['ProductName'] ) . "', Description = '" . mysql_real_escape_string ($_POST['Description'] ) . "', images = '" . mysql_real_escape_string ($ImageStr ) . "', Price = '" . mysql_real_escape_string ($_POST['Price'] ) . "', DatePosted = '" . mysql_real_escape_string ($t ) . "', DateExp = '" . mysql_real_escape_string ($_SESSION['AccountExpDate'] ) . "', FeaturedStatus = '" . mysql_real_escape_string ($_POST['sp'] ) . "' "; PHP: