Freakin' Quotes and Apostrophes... using INSERT

[andrewpike2000]

I am having some real problems finding a solid, yet simple solution for this.  I'm trying to INSERT several variables into my MySQL database.  The problem I have is with some of the content included in the insert, as I'm sure you already realize....

 

Here's a sample...

 

$query = "INSERT INTO `mytable` (`a`, `b`, `c`, `d`) VALUES ('$a', '$b', '$c', '$d')";

 

The problem, for me, is that the variable `c` has content that includes " and ' throughout.  Such as this...

 

My Mom's dog likes "REAL" cat food.

 

I've tried everything from mysql_real_escape_string to the add slashes / remove slashes thing, etc.  It's been driving me crazy for several hours now... SEVERAL.  The field is a varchar field and although the INSERT inputs the data into the database, it's cut off because of the quotes and apostrophe's in it. 

 

Everything I read on the net says to use mysql_real_escape_string for security reasons, but I can't seem to make it work for me.  How should it be applied to this  INSERT or how can I get this content into my DB in-tact?

 

[toplay]

You should have posted the code you tried so we can help show you were you might have went wrong.

 

Use mysql_real_escape_string() and it doesn't matter if your enclosing quotes in the query are single or double quotes.

 

Example:

<?php

// connect to MySQL server, select DB and verify all calls were successful

$c = mysql_real_escape_string($c); // Repeat/do for all columns

$query = "INSERT INTO `mytable` (`a`, `b`, `c`, `d`) VALUES ('$a', '$b', '$c', '$d')";
$result = mysql_query($query);

if ($result) {
    echo 'Row added OK';
} else {
    echo 'SQL error: ', mysql_error(), ' SQL: ', $query;
    exit;  // for debugging
}

?>

 

 

 

 

FYI:

 

http://www.phpfreaks.com/forums/index.php/topic,176611.msg784012.html#msg784012

[andrewpike2000]

Well, I tried that suggestion and I gotthe same result.  The data is inserted into the database, incomplete and cut off where quotes would typically be...  Here's the actual code.  ...No errors received.

 

<?php
include("_db_connect.php");

$ci = $_POST['ci']; // CATEGORY ID
$si = $_POST['si']; // SUBCATEGORY ID
$in = $_POST['in']; // ITEM NUMBER
$cs = $_POST['cs']; // ITEM COST
$pr = $_POST['pr']; // PRICE
$sp = $_POST['sp']; // SALE PRICE
$dp = $_POST['dp']; // DEALER PRICE
$av = $_POST['av']; // AVAILABILITY ID
$sh = $_POST['sh']; // SHIP PRICE
$sw = $_POST['sw']; // SHIP WEIGHT
$sdx = $_POST['sd']; // SHORT DESCRIPTION
$ldx = $_POST['ld']; // LONG DESCRIPTION
$twx = $_POST['tw']; // TWIST
$ssx = $_POST['ss']; // STYLE / SIZE
$cax = $_POST['ca']; // CALIBER
$sd = mysql_real_escape_string($sdx);
$ld = mysql_real_escape_string($ldx);
$tw = mysql_real_escape_string($twx);
$ss = mysql_real_escape_string($ssx);
$ca = mysql_real_escape_string($cax);

$query = "INSERT INTO `items` (`item_id`, `item_cat_id`, `item_subcat_id`, `item_desc`, `item_caliber`, `item_twist`, `item_style_size`, `item_price`, `item_price_sale`, `item_avail`, `item_desc_long`, `item_ship_price`, `item_ship_weight`, `item_cost`, `item_price_dealer`) VALUES ('$in', '$ci', '$si', '$sd', '$ca', '$tw', '$ss', '$pr', '$sp', '$av', '$ld', '$sh', '$sw', '$cs', '$dp')";
$result = mysql_query($query);

if ($result) {
    echo 'Row added OK';
} 
else{
    echo 'SQL error: ', mysql_error(), ' SQL: ', $query;
    exit;  // for debugging
}

include("_db_disconnect.php");
?>

[toplay]

I was about to ask you for what version of MySQL you have, until I saw it in your forum signature.

 

If you are really using MySQL version 5+, then you need to be using mysqli_xxx type of functions and not mysql_xxx.

 

http://us.php.net/manual/en/function.mysqli-real-escape-string.php

 

 

[andrewpike2000]

I guess I'm an idiot.  I don't understand.  I can insert everything else into this database table just fine... except when quotes or apostrophe's are in the variable.  This particular site was just recently upgraded to 5.0.45 and none of the other scripts I have in place have required a change...

 

I just don't understand.

[toplay]

First change ALL your mysql_xxx functions to mysqli_xxx instead.

 

Then help yourself debug this by displaying the query. See if the quotes are being backslashed ok.

 

...

echo $query; // Display for debugging

$result = mysql_query($query);

...

 

A value that has quotes should look something like this:

 

... , 'My Mom\'s dog likes \"REAL\" cat food.', ...

 

 

 

[andrewpike2000]

I will do that.  I'll follow-up on this post once I get this figured out... (with a little help from my 'PHPfreak' friends).  Thanks for your help.

[andrewpike2000]

Do you happen to know if I can just adjust the text of  mysql_ with mysqli_ without having to do a ton of reconstruction to the parameters in the functions?  In other words, just do a find/replace sort of thing...?  Or has it changed much more than that?

[andrewpike2000]

And I just pulled the echo of the query and figured I would show you what I'm getting...

 

INSERT INTO `items` (`item_id`, `item_cat_id`, `item_subcat_id`, `item_desc`, `item_caliber`, `item_twist`, `item_style_size`, `item_price`, `item_price_sale`, `item_avail`, `item_desc_long`, `item_ship_price`, `item_ship_weight`, `item_cost`, `item_price_dealer`) VALUES ('111-999', '0001', '0002', 'The ', '', '', '', '9.99', '6.99', '0001', 'This is Mike\'s test.', '', '', '1.00', '')

 

The green is where it failed on a " and the red seems to be OK with a ' only.  When I run the same query from the phpMyAdmin interface I see it takes a ' and turns it into \'\' and a " it doesn't do anything to it.  Of course, the query is enclosed in '

[toplay]

Do you happen to know if I can just adjust the text of  mysql_ with mysqli_ without having to do a ton of reconstruction to the parameters in the functions?  In other words, just do a find/replace sort of thing...?  Or has it changed much more than that?

For the most part, yes. But there are some functions that are different, require arguments as opposed to optional, or arguments might be in different order. So refer to the php.net site.

 

The mysqli_real_escape_string() requires two arguments which is different than it's mysql_xxx version:

 

http://us.php.net/manual/en/function.mysqli-real-escape-string.php

 

 

That's weird about the green value you posted.

 

'The "double quotes should be fine inside single quotes" so I don\'t get it'

 

Show what the full value of it looks like and mention what collation or character set is the table and data. Better post most current relevant code again too.

 

 

[toplay]

INSERT INTO `items` (`item_id`, `item_cat_id`, `item_subcat_id`, `item_desc`, `item_caliber`, `item_twist`, `item_style_size`, `item_price`, `item_price_sale`, `item_avail`, `item_desc_long`, `item_ship_price`, `item_ship_weight`, `item_cost`, `item_price_dealer`) VALUES ('111-999', '0001', '0002', 'The ', '', '', '', '9.99', '6.99', '0001', 'This is Mike\'s test.', '', '', '1.00', '')

 

You're echoing the query after the mysqli_escape_string() and what will be used in the mysqli_query(), so since you're  seeing only 'The ' means that it's not being populated with the whole entire value to begin with. So, it's not a MySQL problem and mysqli_query() is not the one truncating anything.

 

Look elsewhere in your code and display the value(s) of what you think you're getting from your HTML form, especially $_POST['sd'].

 

Also, you got to know what your get_magic_quotes_gpc() setting is. When it's on, PHP will already have backslashed the quotes (and that means you shouldn't do it yourself in the code).

 

 

 

[andrewpike2000]

I appreciate the follow-up on that.  That is indeed the issue.  I'm losing the form value somehow from one page to the next.  The value is fine from Page 1 to Page 2, but not from Page 2 to Page 3.  Not sure why, but I know this doesn't have anything to do with MySQL so I'll try to figure that out on my own...

 

Here is the order of the pass:

 

Step 1: Form field filled out... and using POST to go to PREVIEW page.

 

<?php
echo "<form action=\"_admin_add_item_CONFIRM.php\" method=\"post\">";
echo "<input name=\"sd\" type=\"text\" size=\"60\" maxlength=\"100\">";
echo "</form>";
?>

 

Step 2: Preview of submission presented.  I then try to take that variable that's passed and put it in a new $POST_ variable for submission to the DB...

 

<?php
$sd = $_POST["sd"]; // SHORT DESCRIPTION
echo $sd;

echo "<form action=\"_admin_dbinsert.php\" method=\"post\">\n";
echo "<input name=\"sd\" type=\"hidden\" value=\"". $sd ."\">\n";
echo "</form>\n";
?>

 

Step 3 is the INSERT into the DB.

 

<?php
$sd = mysql_real_escape_string($_POST['sd']); // SHORT DESCRIPTION

include("_db_connect.php");
$query = "INSERT INTO `items` (`item_desc`) VALUES ('$sd')";
$result = mysql_query($query) or die("Query Failed: ". mysql_error());
include("_db_disconnect.php");
?>

 

Suggestions welcome... And thanks again.  It's always those "little" oversights that seem to get me.

[toplay]

Try something like:

 

Step 2:

<?php
$sd = (get_magic_quotes_gpc()) ? $_POST["sd"] : addslashes($_POST["sd"]); // or mysqli_real_escape_string() if DB connection is open

echo $sd;

echo '<form action="_admin_dbinsert.php" method="post">', "\n";
echo '<input name="sd" type="hidden" value="', $sd, '">', "\n";
echo "</form>\n";
?>

 

Step 3:

<?php
include("_db_connect.php");  // Must use mysqli_xxx functions - set $db_link

$sd = (get_magic_quotes_gpc()) ? $_POST['sd'] : mysqli_real_escape_string($db_link, $_POST['sd']); // SHORT DESCRIPTION

$query = "INSERT INTO `items` (`item_desc`) VALUES ('$sd')";
$result = mysqli_query($db_link, $query) or die("Query Failed: ". mysqli_error());

include("_db_disconnect.php"); // Use mysqli_xxx
?>

 

get_magic_quotes_gpc() manual page:

http://us.php.net/manual/en/function.get-magic-quotes-gpc.php