Can't insert text into a db where it contains "'s"

Question

I have the following insert method:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
     foreach ($_POST['Category'] as $value){
   $Full_Description = $_POST['Full_Description'];
$Specification = $_POST['Specification'];
$BriefDescription = $_POST['BriefDescription'];
$MPN = $_POST['MPN'];
$GTIN = $_POST['GTIN'];
$Product = $_POST['Product'];
$Color = $_POST['Color'];
$Model = $_POST['Model'];
$Manufacturer = $_POST['Manufacturer'];
$Category = $_POST['Category'];
$submitted = $_POST['submit'];  
  $insertSQL = sprintf("INSERT INTO products (Category, Manufacturer, Model, Color, Product, GTIN, MPN, BriefDescription, Specification, Full_Description) VALUES ('$value', '$Manufacturer','$Model','$Color','$Product','$GTIN','$MPN','$BriefDescription','$Specification','$Full_Description')",
                       GetSQLValueString($_POST['Category'], "text"),
                       GetSQLValueString($_POST['Manufacturer'], "text"),
                       GetSQLValueString($_POST['Model'], "text"),
                       GetSQLValueString($_POST['Color'], "text"),
                       GetSQLValueString($_POST['Product'], "text"),
                       GetSQLValueString($_POST['GTIN'], "text"),
                       GetSQLValueString($_POST['MPN'], "text"),
                       GetSQLValueString($_POST['BriefDescription'], "text"),
                       GetSQLValueString($_POST['Specification'], "text"),
                       GetSQLValueString($_POST['Full_Description'], "text");

  mysql_select_db($database_dconn, $dconn);
  $Result1 = mysql_query($insertSQL, $dconn) or die(mysql_error());
}}

The database field is: Full_Description longtext

Inserting data is working fine until the Full_Description contains a "'s". the error that comes up is:

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

I do not know why. Is there anybody who can help? I am very sorry if it is obvious, however I am really struggling Any help welcome

use mysqli or prdo with prepared statement, mysql_* is deprecated — , Jan 06 '15 at 12:11
Aside from all the issues with your MySQL-related code, that's absolutely not how `sprintf` works. Read [the manual page for `sprintf`](http://php.net/sprintf), especially the examples. — DCoder, Jan 06 '15 at 12:24
there is no way that code would be working "fine" even without the `'s`, as it is not using sprintf parameters at all. — eis, Jan 06 '15 at 14:31

score 2 · Accepted Answer · answered Jan 06 '15 at 12:08

2

Use mysql_real_escape_string() before insert your string.

$item = "Zak's and Derick's Laptop";
$escaped_item = mysql_real_escape_string($item, $link);
printf("Escaped string: %s\n", $escaped_item);

see more here: http://php.net/manual/en/function.mysql-real-escape-string.php

answered Jan 06 '15 at 12:08

Luis Simioni

161
6

I have added the full code what I've got, I'm just not sure what I'm doing wrong – Ria Jan 06 '15 at 12:14
I have read up on this and all working now thanks so much for your help – Ria Jan 07 '15 at 10:51

eis · Answer 2 · 2015-01-06T12:42:26.937

0

Edit:

Rewrote my answer, since your problem seems to actually be your sprintf usage, not your escaping. You use it like this:

 $insertSQL = sprintf("INSERT INTO products (Category, Manufacturer, Model, Color, Product, GTIN, MPN, BriefDescription, Specification, Full_Description) VALUES ('$value', '$Manufacturer','$Model','$Color','$Product','$GTIN','$MPN','$BriefDescription','$Specification','$Full_Description')",

But for it to work, you should use it like this:

 $insertSQL = sprintf("INSERT INTO products (Category, Manufacturer, Model, Color, Product, GTIN, MPN, BriefDescription, Specification, Full_Description) VALUES ('%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s')",

See sprintf manual for more details.

(PS. It should be also noted that you should really use connection parameter to mysql_real_escape_string as well to take character set into account, or use prepared statements. But that's an improvement and not related to your issue.)

edited Jan 06 '15 at 12:42

answered Jan 06 '15 at 12:06

eis

49,147
13
140
191

I have added the full code what I've got, I'm just not sure what I'm doing wrong – Ria Jan 06 '15 at 12:14
@Ria edited my answer based on your code. – eis Jan 06 '15 at 12:42
I've tried your changes, however that didn't solve the problem. If i do the insertion without the foreach loop it works fine. Hope you can help me. – Ria Jan 06 '15 at 13:17
@Ria I'm quite confident that it will solve the error you have posted. The problem with foreach loop is a different one, you don't seem to be using the foreach `$value` in anywhere meaningful. – eis Jan 06 '15 at 14:27
@Ria it does seem that your code really isn't making a lot of sense at all... you haven't explained even what the forloop is meant to do or what is it used for. The error message you have posted should be fixed with what I posted, but you have a lot of logical problems with the code as well, which cannot be solved with the information you've given. – eis Jan 06 '15 at 14:30
the for each loop needs to insert for each Category value a new row in the db. The code I have given is all the code that I'm using there is no more. Hope that helps. I probably am not using the for each loop right as a normal insertion without the for each loop work fine. – Ria Jan 06 '15 at 14:34

Can't insert text into a db where it contains "'s"

2 Answers2