[RESOLVED] mysql_real_escape_string problems

Maharg105 · Mar 26, 2010

Hi again people,

I've got a wee problem with the above mentioned problem and as far as I can gather, I've put everything in place to solve it. But it's still not working.

I have a CMS form for inputting new news articles. These will often contain a mixture of double and single quotes.

I have the following code in place for when the variable is being added to the database

$title=mysql_real_escape_string($_POST['title'],$con);

and then this code for when I am displaying it

$title=htmlspecialchars($title2);

But non of this seems to be working. When I look in the database itself. Backslashes don't appear to have been appended to double quotes (though they appear on the website fine) and the string is simply cut off at the first instance of a single quote.

I really need to get this working and have been left scratching my head :s
Any help anyone can proffer would be gratefully received

Thanks,

igorek · Mar 26, 2010

try

// check for magic_quotes_gpc
function mysql_prep($value){
	$magic_quotes_active = get_magic_quotes_gpc();
	$new_enough_php = function_exists("mysql_real_escape_string");
// i.e. PHP >= v4.3.0
	if($new_enough_php){ // PHP v4.3.0 or higher
// undo any magic quote effects so mysql_real_escape_string can do the work
		if($magic_quotes_active){ $value=stripslashes($value); }
		$value=mysql_real_escape_string($value);
		} 
	else { // before PHP v4.3.0
// if magic quotes arent already on then add slashes manually
		if(!$magic_quotes_active){$value=addslashes($value);}
// if magic quotes are active, then the slashes already exist
		}
	return $value;
}

// in action
$a = mysql_prep($_REQUEST['a'];

also echo your $query to debug and see what happens before it goes into db.

Maharg105 · Mar 26, 2010

Hi Igorek,

Thanks for dropping in. I put the function that you, very kindly, suggested. The $a = mysql_prep($_REQUEST['a']; element completely throws my script off and I get a "unexpected ;" warning, but this is probably because I don't properly understand it's purpose and have improperly implemented it.

If I remove that though. I get the same problem. If I input the string "Quote's Test" and run it through your function and then echo the string, I get Quote and the rest is cut off.

I forgot to mention before that I disabled magic quotes, so it shouldn't be this causing the problem.

Thanks again,

djjjozsi · Mar 26, 2010

check your field's size in your MYSQl table.

While you'r testing, this should use instead if magic qoutes has set OFF

$title=mysql_real_escape_string($_POST['title'],$con);

If you keep that user created function, add a closing ")" at the end

$title = mysql_prep( $_POST['title'] );

Maharg105 · Mar 26, 2010

Thanks for the advice djjjozsi,

I'm not sure what the problem is here. Whether I use the mysql_prep function or mysql_real_escape_string the same thing happens, the string is cut off at the first single quote.

If I echo the string before this element of the script gets hold of it, it is still intact. The string remains intact and can be retrieved and displayed in it's entirety if I enter it without the quotes.

Not really sure where to go from here. Thanks again for the help guys and apologies if I'm being a bit simple

djjjozsi · Mar 26, 2010

First, echo your SQL insert query, and post it here what you get.

Then see the entered data in PHPMYADMIN if its there.

Then give us the real example how you want to echo it:

at the first single quote.

Maharg105 · Mar 26, 2010

Thanks again for the help,

If I echo my Insert query I get

INSERT INTO press (title, front_cover, article, notes, publication) VALUES ('Echo Test','images/press/covers/SH_13e41b3a2262e1a21.jpg','images/press/articles/SH_40e41b3a2262e1a21.jpg','','Daily Echo')

So obviously the problem is that the single quote isn't being escaped and it ends that part of the variable. As the string I actually entered for title was Echo Test'ing

I don't understand why, if I use mysql_real_escape_string, it isn't escaping that single quote.

Any ideas?

Maharg105 · Mar 26, 2010

I have also tried using addslashes() which correctly processes the string. But then my insert query fails.

djjjozsi · Mar 26, 2010

When you looked into the table, that apostrofe should not really escaped.

If you want to insert an apostrofe in an SQL string, you have to escape it.

Here is an example to handle empty values:

<?php
function mres( $string )
{
    if ( get_magic_quotes_gpc() )
        $string = stripslashes( $string );

return mysql_real_escape_string( $string );
} 

if ( isset( $_POST["submit"] ) )
{
    /* add the apostrofes around the not empty values*/
    $title = isset( $_POST["title"] )? "'" . mres( $_POST["title"] ) . "'" : 'NULL';
    $front_cover = isset( $_POST["front_cover"] )? "'" . mres( $_POST["front_cover"] ) . "'" : 'NULL';
    $article = isset( $_POST["article"] )? "'" . mres( $_POST["article"] ) . "'" : 'NULL';
    $notes = isset( $_POST["notes"] )? "'" . mres( $_POST["notes"] ) . "'" : 'NULL';
    $publication = isset( $_POST["publication"] )? "'" . mres( $_POST["publication"] ) . "'" : 'NULL';

$sql = "insert into `press` (`title`,`front_cover`,`article`,`notes`,`publication`) 
                   VALUES ($title , $front_cover , $article , $notes , $publication )";

$res = mysql_query( $sql ) or die( mysql_error() );
echo "Inserted...";
} 

?>

Maharg105 · Mar 26, 2010

If I use the following code

$con = mysql_connect("localhost","grah2005_super54","jewellery3d17");
	if (!$con)
  	{
  	die('Could not connect: ' . mysql_error());
 	 }

 mysql_select_db("grah2005_shannon", $con);

 /* add the apostrofes around the not empty values*/ 
$title = isset( $_POST["title"] )? "'" . mres( $_POST["title"] ) . "'" : 'NULL'; 
$front_cover = isset( $_POST["front_cover"] )? "'" . mres( $_POST["front_cover"] ) . "'" : 'NULL'; 
$article = isset( $_POST["article"] )? "'" . mres( $_POST["article"] ) . "'" : 'NULL'; 
$notes = isset( $_POST["notes"] )? "'" . mres( $_POST["notes"] ) . "'" : 'NULL'; 
$publication = isset( $_POST["publication"] )? "'" . mres( $_POST["publication"] ) . "'" : 'NULL';

 $sql="INSERT INTO press (title, front_cover, article, notes, publication) 
 		VALUES
		('$title','$filecover','$fileart','$notes','$publication')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
mysql_close($con);

with the mres function being declared at the beginning. I am presented with the following 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 'Grazia Doub'','','','''',''Grazia Weekly'')' at line 3

The title string I used was Grazia Doub'le Test.

djjjozsi · Mar 26, 2010

but i warned you about the apostrofes...

plus, where have you taken my mres function?

<?php
$con = mysql_connect( "localhost", "xxxxxxxxxxxxxxxxxxxxx", "xxxxxxxxxxxxxxx" );
if ( !$con )
{
    die( 'Could not connect: ' . mysql_error() );
} 

mysql_select_db( "xxxxxxxxxx", $con );

/* add the apostrofes around the not empty values */
$title = isset( $_POST["title"] )? "'" . mres( $_POST["title"] ) . "'" : 'NULL';
$front_cover = isset( $_POST["front_cover"] )? "'" . mres( $_POST["front_cover"] ) . "'" : 'NULL';
$article = isset( $_POST["article"] )? "'" . mres( $_POST["article"] ) . "'" : 'NULL';
$notes = isset( $_POST["notes"] )? "'" . mres( $_POST["notes"] ) . "'" : 'NULL';
$publication = isset( $_POST["publication"] )? "'" . mres( $_POST["publication"] ) . "'" : 'NULL';

$sql = "INSERT INTO press (title, front_cover, article, notes, publication)
             VALUES
            ($title , $filecover , $fileart , $notes , $publication )";

if ( !mysql_query( $sql, $con ) )
{
    die( 'Error: ' . mysql_error() );
} 
mysql_close( $con );

?>

robot_nader · Mar 31, 2010

Hi Maharg105,

How did you solve this problem? I am faced same problem.
Please, let me know if you have any solution.

Thanks,
Nader

Maharg105 · Mar 31, 2010

Hey robot_nader,

I'm currently working on a solution that I will have the kinks worked out of by tomorrow. I'll post again when I've got the solution if you like?

Thanks,

robot_nader · Mar 31, 2010

Hi Maharg105,

Thanks for fast reply. I am impatiently waiting for that solution. that is a great share. I have seen many people in the net have the same problem.

Thanks,

bradgrafelman · Apr 1, 2010

But djjjozsi already posted the solution 5 days ago...

Maharg105 · Apr 1, 2010

Hi Brad,

Apologies, I obviously missed (or likely didn't understand) the solution. As far as I could gather djjjozsi gave me the mres function that would handle empty variables. Then told me to escape strings to combat the problem with inserting quotes into the database?

I escaped the strings, but instead of inserting the entire escaped string into the database it simply cuts the string off when it comes to a quote. A further more perplexing problem is that it only does this on my scripts that insert information into the database, where it is updating an existing record then the quotation marks (and any other escaped characters) are succesfully inserted.

However, when it is a script that inserts a new record. I am presented with the ,aforementioned, problem of the string being cut off at the first escaped character.

The code for the update script 'that works' is;

$con = mysql_connect("localhost","username","password");
		if (!$con)
  		{
  		die('Could not connect: ' . mysql_error());
 	 	}

 	mysql_select_db("database", $con);

	$code = mysql_real_escape_string($code, $con);
 	$name = mysql_real_escape_string($name, $con);
 	$price = mysql_real_escape_string($price, $con);
 	$description = mysql_real_escape_string($description, $con);
 	$material = mysql_real_escape_string($material, $con);
 	$etsy = mysql_real_escape_string($etsy, $con);

 	$sql="UPDATE products SET product_code='$code', product_name='$name', price='$price', product_description='$description', 		product_collection='$collection', product_category='$category', product_material='$material', etsy_link='$etsy', product_picture='$picture', product_thumbnail='$thumbnail' WHERE product_id='$id'";

	if (!mysql_query($sql,$con))
	{
	die('Error: ' . mysql_error());
	}
	mysql_close($con);

The code for the insert script that 'isn't working' is;

$con = mysql_connect("localhost","username","password");
	if (!$con)
  	{
  	die('Could not connect: ' . mysql_error());
 	 }
mysql_select_db("database", $con);

 $code = mysql_real_escape_string($code, $con);
 $name = mysql_real_escape_string($name, $con);
 $price = mysql_real_escape_string($price, $con);
 $description = mysql_real_escape_string($description, $con);
 $material = mysql_real_escape_string($material, $con);
 $etsy = mysql_real_escape_string($etsy, $con);

 $sql="INSERT INTO products (product_code,product_name, price, product_description, product_collection, product_category, product_picture, product_thumbnail, product_material, etsy_link) 
 		VALUES
		('$code','$name','$price','$description','$collection','$category','$file','$file_thumb','$material','$etsy')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
mysql_close($con);

Apologies again if this has already been explained, but it is somewhat perplexing me.

As always, I'm extremely grateful for the help,

djjjozsi · Apr 1, 2010

in your INSERT string how you have put the posted variables?
If you echo the $sql string, you will see which variables goes into the string.

echo $sql;

using this little code, you get all the errors and warnings in your program:

<?php 	
error_reporting(E_ALL);
ini_set("display_errors", 1); 
?>

If you have "undefined index" messsages, you have to see these lines.

bradgrafelman · Apr 1, 2010

Previously your problem was that you were adding quotes to the variables themselves and in the SQL query string where the variables were being inserted.

As djjjozsi suggests, if you're having problems with the above INSERT query, echo out the query string as well as the error message - that way we can see where MySQL thinks the problem is and what the whole string looks like.

Maharg105 · Apr 2, 2010

I put error checking in place and echo'd the sql query and this is what I got

Notice: Use of undefined constant ******** - assumed '********' in /home/grah2005/public_html/shannonhazell/CMS/addproduct_changes.php on line 6
INSERT INTO products (product_code,product_name, price, product_description, product_collection, product_category, product_picture, product_thumbnail, product_material, etsy_link) VALUES ('Br7864','April','456.78','This is a CMS product addition test\r\n\r\nThis will test the ability \"to escape characters\" before they are inserted into the database','Gemstone','Bracelets','images/products/Gemstone//SH_296e0a07319f1049e_jpg','images/products/Gemstone/thumb//SH_296e0a07319f1049e_jpg','Silver and Gemstone','')

The undefined constant it is referring to, is a session variable that keeps track of which pages the user has access rights to. This is on all of the pages, so it's doubtful that it's this causing the problem.

The query itself is completely intact. Except that the second insert value was supposed to be April's Wonder. So again, the string has been cut off at the first instance of a single quote. Getting the single quotes into the database seems to be the last remaining problem with regards to this issue?

Any ideas, because I'm left scratching my head.

laserlight · Apr 2, 2010

$name must come from somewhere, e.g., an incoming variable like $_POST['name']. Check that incoming variable... does it actually contain "April's Wonder" instead of just "April"?

[RESOLVED] mysql_real_escape_string problems

MMaharg105

Iigorek

MMaharg105

Ddjjjozsi

MMaharg105

Ddjjjozsi

MMaharg105

MMaharg105

Ddjjjozsi

MMaharg105

Ddjjjozsi

Rrobot_nader

MMaharg105

Rrobot_nader

Bbradgrafelman

MMaharg105

Ddjjjozsi

Bbradgrafelman

MMaharg105

laserlight