darubillah Posted February 5, 2011 Share Posted February 5, 2011 I am getting the following error when performing insertion in mysql db Error: 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 Editor's Choice * Secures against internal and external attacks * Bl' at line 1 This is my script, I cant figure out the prob, some time it insert record successfully and sometime not. <?php $con = mysql_connect("localhost","userName","PassWord"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("db_name", $con); $sql = "INSERT INTO `ura_items` (`ProductName`, `CategoryID`, `ShortDesc`, `LongDesc`, `TrialURL`, `Boxshot`, `Screenshot`, `VendorID`, `VendorName`, `VendorHomepageURL`, `Add_Date`, `size`, `soft_type`, `current_ver`) VALUES("; $sql .= "'$_POST[ProductName]', '$_POST[CategoryID]', '$_POST[shortDesc]', '$_POST[LongDesc]', '$_POST[TrialURL]', '$_POST[boxshot]', '$_POST[screenshot]', '$_POST[VendorID]', '$_POST[VendorName]', '$_POST[VendorHomepageURL]', now(), '$_POST[size]', '$_POST[soft_type]', '$_POST[current_ver]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added <br /> <br /> <a href=\"addproduct.php\">Back</a>"; mysql_close($con) ?> Please help me Quote Link to comment Share on other sites More sharing options...
PaulRyan Posted February 5, 2011 Share Posted February 5, 2011 You haven't properly escaped the data you're inserting... Use mysql_real_escape_string(); to make sure you escape the data double and single quotes. Regards, PaulRyan, Quote Link to comment Share on other sites More sharing options...
darubillah Posted February 5, 2011 Author Share Posted February 5, 2011 can u please tell me how to do it, coz i am very new to PHP Quote Link to comment Share on other sites More sharing options...
harristweed Posted February 5, 2011 Share Posted February 5, 2011 try removing the quotes from the table name "INSERT INTO ura_items ( Not "INSERT INTO `ura_items` ( and use straight quote on the field names ('ProductName', Not(`ProductName`, Quote Link to comment Share on other sites More sharing options...
PaulRyan Posted February 5, 2011 Share Posted February 5, 2011 Actually you can use the ` symbol for the query Harristweed, I use them myself, allows for easier read-ability. Now back on topic, instead of just using $_POST to insert data straight away, assign the $_POST values to variables then do some validation. For example //Insert of... $_POST[ProductName] // Use the following $productName = mysql_real_escape_dtring($_POST['ProductName']); Do that for each variable if you feel it may have data that needs to be escaped, then use the variables within the query instead of the $_POST data Regards, PaulRyan. Quote Link to comment Share on other sites More sharing options...
darubillah Posted February 5, 2011 Author Share Posted February 5, 2011 @harristweed done as you said above but now it show this error Error: 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 ''ProductName', 'CategoryID', 'ShortDesc', 'LongDesc', 'TrialURL', 'Boxshot', 'Sc' at line 1 Quote Link to comment Share on other sites More sharing options...
darubillah Posted February 5, 2011 Author Share Posted February 5, 2011 Oh I got that Paul lemme try it, actually with out using mysql_real_escape_dtring() it may breaking my query coz i have quote text fields i'll back Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 5, 2011 Share Posted February 5, 2011 try removing the quotes from the table name "INSERT INTO ura_items ( Not "INSERT INTO `ura_items` ( and use straight quote on the field names ('ProductName', Not(`ProductName`, No, no, no. When enclosing field and table names, backticks are used. Single quotes are used on string type data values. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted February 5, 2011 Share Posted February 5, 2011 Those marks around the table name the field names are not quotes, they are backticks and are OK. To the OP, you are using the values from your form directly in your query without sanitizing them at all. This is very dangerous. Always validate and sanitize user input. Since your form field names are the same as your DB field names, I would use a foreach loop to create the query: <?php $qtmp = array(); foreach ($_POST as $f => $v) { if ($f != 'submit') { // don't do this for the submit button if (strlen($v) != 0) { // don't need to store empty values $qtmp[] = "$f = '" . mysql_real_escape_string($v) . "'"; } } } $qtmp[] = "add_date = NOW()"; // build query using the alternate insert syntax $q = 'insert into `ura_items`set ' . implode(', ',$qtmp); $rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error()); ?> Note: not tested for syntax errors. Ken Quote Link to comment Share on other sites More sharing options...
darubillah Posted February 5, 2011 Author Share Posted February 5, 2011 COoool Thnx every1 for solving my prob, n @kenrbnsn you code look too awesome, but i cant figure out how to use it my prob is solved but still wanna know how to use it. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted February 5, 2011 Share Posted February 5, 2011 Replace your code <?php $sql = "INSERT INTO `ura_items` (`ProductName`, `CategoryID`, `ShortDesc`, `LongDesc`, `TrialURL`, `Boxshot`, `Screenshot`, `VendorID`, `VendorName`, `VendorHomepageURL`, `Add_Date`, `size`, `soft_type`, `current_ver`) VALUES("; $sql .= "'$_POST[ProductName]', '$_POST[CategoryID]', '$_POST[shortDesc]', '$_POST[LongDesc]', '$_POST[TrialURL]', '$_POST[boxshot]', '$_POST[screenshot]', '$_POST[VendorID]', '$_POST[VendorName]', '$_POST[VendorHomepageURL]', now(), '$_POST[size]', '$_POST[soft_type]', '$_POST[current_ver]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } ?> with my code. Ken Quote Link to comment Share on other sites More sharing options...
PaulRyan Posted February 5, 2011 Share Posted February 5, 2011 @Ken Your code will fail if any of the $_POST fields have a single quote in, or are injected with SQL. You need to ecape this data use the mysql_real_escape_string() function, or something similar. Regards, PaulRyan. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted February 5, 2011 Share Posted February 5, 2011 Read my code again. I use mysql_real_escape_string on each value in the $_POST array. <?php if (strlen($v) != 0) { // don't need to store empty values $qtmp[] = "$f = '" . mysql_real_escape_string($v) . "'"; } ?> Ken Quote Link to comment Share on other sites More sharing options...
PaulRyan Posted February 5, 2011 Share Posted February 5, 2011 My bad, I reliased you were quoting his code the mid section... apologies! Replace your code <?php $sql = "INSERT INTO `ura_items` (`ProductName`, `CategoryID`, `ShortDesc`, `LongDesc`, `TrialURL`, `Boxshot`, `Screenshot`, `VendorID`, `VendorName`, `VendorHomepageURL`, `Add_Date`, `size`, `soft_type`, `current_ver`) VALUES("; $sql .= "'$_POST[ProductName]', '$_POST[CategoryID]', '$_POST[shortDesc]', '$_POST[LongDesc]', '$_POST[TrialURL]', '$_POST[boxshot]', '$_POST[screenshot]', '$_POST[VendorID]', '$_POST[VendorName]', '$_POST[VendorHomepageURL]', now(), '$_POST[size]', '$_POST[soft_type]', '$_POST[current_ver]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } ?> with my code. Ken Regards, PaulRyan. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.