Escape special characters

[Christos]

Hello

I'm trying to insert some data to mysql database but when the name of the products contains a special character like ' the query fails to execute.

I tried to use mysql_real_escape_string but with no luck.
Can anyone help me to escapes special characters from a specific field (product_name) ?

Bellow is the code i'm using to insert the data.

 

Any help would be very much appreciated

$q = "INSERT INTO `#__publishers_links` (order_id, server_id, publisher_id, secret_key, product_id, product_name, product_price, download_url, date_created, payment) VALUES ";
		foreach ($cart->products as $product) {
			
			if ($product->dl_unlocked == '1' || $product->dl_unlocked == 1) {
				if ($queryflag == 0) {
					$q .= "('$ass_orderID','','$product->virtuemart_manufacturer_id','','$product->virtuemart_product_id','$product->product_name','$product->product_price','$product->dl_ebook_url','".gmdate("Y-m-d H:i:s")."','0')";
					$queryflag ++;
				} else {
					$q .= ",('$ass_orderID','','$product->virtuemart_manufacturer_id','','$product->virtuemart_product_id','$product->product_name','$product->product_price','$product->dl_ebook_url','".gmdate("Y-m-d H:i:s")."','0')";
					$queryflag ++;
				}
			} else {			
				$product->dl_ebook_url = $serverID->ebook_url;
				if ($product->dl_server_id != 0 && $product->dl_server_id != '0') {
					$link_url = $product->dl_link_url.'?';
					$book_url = 'action=enterorder&ordersource='.urlencode($product->dl_order_source);
					if ($product->dl_publisher_order_format == '' || $product->dl_publisher_order_format == 'NULL') {
						$book_url .= '&orderid='.urlencode($ass_orderID).'-'.($product->virtuemart_product_id);
					} else {
						$replace = str_replace('[XXX]', $ass_orderID, $product->dl_publisher_order_format);
						$book_url .= '&orderid='.urlencode($replace).'-'.($product->virtuemart_product_id);	
					}
					$dateval=time();
					$gbauthdate=date('m/d/Y');
					$Secret = $product->dl_shared_secret;
					if ($product->dl_pdf_id != '') {
						$book_url .= '&resid='.urlencode('urn:uuid:'.$product->dl_pdf_id);
					} else if ($product->dl_epub_id != '') {
						$book_url .= '&resid='.urlencode('urn:uuid:'.$product->dl_epub_id);
					} else { //throwing an alert - ti allo?
						$document =& JFactory::getDocument();
						$document->addScriptDeclaration ("
							alert('xxxxxxxxxxxxxxxxxxxx');
						");
					}
					$book_url .= '&gbauthdate='.urlencode($gbauthdate);
					$book_url .= '&dateval='.urlencode($dateval);
					$book_url .= '&gblver=4';
					$book_url = str_replace('%2D','-',$book_url);
					$download_link = $link_url.$book_url."&auth=".hash_hmac("sha1", $book_url, base64_decode($Secret));
					if ($queryflag == 0) {
						$q .= "('$ass_orderID','$product->dl_server_id','$product->virtuemart_manufacturer_id','$product->dl_shared_secret','$product->virtuemart_product_id','$product->product_name','$product->product_price','$download_link','".gmdate("Y-m-d H:i:s")."','0')";
						$queryflag ++;
					} else {
						$q .= ",('$ass_orderID','$product->dl_server_id','$product->virtuemart_manufacturer_id','$product->dl_shared_secret','$product->virtuemart_product_id','$product->product_name','$product->product_price','$download_link','".gmdate("Y-m-d H:i:s")."','0')";
					}
				}
			}

[Irate]

Try htmlspecialchars() to escape all ', ", < and > within submitted data.

I would make an example but my mobile phone is very limited there.

[floridaflatlander]

http://php.net/manual/en/function.htmlentities.php

 

note the flags constants, I use ENT_QUOTES

[Irate]

Where do you that line?

I do not see it anywhere in your code...

[DavidAM]

Try htmlspecialchars() to escape all ', ", < and > within submitted data.

I would make an example but my mobile phone is very limited there.

NO!!!!!

 

htmlspecialchars() is for escaping HTML (hence the name). --- mysql_real_escape_string() is for escaping data for mySql (look, there's the name again).

 

@OP

I don't see you using mysql_real_escape_string in that code at all. In order for it to work, you have to escape each individual variable, not the entire statement (a common noob mistake). It should look something like this:

 

 

 

$q .= ",('" . mysql_real_escape_string($ass_orderID) . "','','" . 
mysql_real_escape_string($product->virtuemart_manufacturer_id) . "','','" . 
mysql_real_escape_string($product->virtuemart_product_id) . "','" . 
mysql_real_escape_string($product->product_name) . "','" . 
mysql_real_escape_string($product->product_price) . "','" . 
mysql_real_escape_string($product->dl_ebook_url) . "','" . 
gmdate("Y-m-d H:i:s") . "','0')";

Although, you don't need it (and should NOT use quotes) for numeric data.

[Irate]

NO!!!!!

 

htmlspecialchars() is for escaping HTML (hence the name). --- mysql_real_escape_string() is for escaping data for mySql (look, there's the name again).

 

@OP

I don't see you using mysql_real_escape_string in that code at all. In order for it to work, you have to escape each individual variable, not the entire statement (a common noob mistake). It should look something like this:

 

 

 

$q .= ",('" . mysql_real_escape_string($ass_orderID) . "','','" . 
mysql_real_escape_string($product->virtuemart_manufacturer_id) . "','','" . 
mysql_real_escape_string($product->virtuemart_product_id) . "','" . 
mysql_real_escape_string($product->product_name) . "','" . 
mysql_real_escape_string($product->product_price) . "','" . 
mysql_real_escape_string($product->dl_ebook_url) . "','" . 
gmdate("Y-m-d H:i:s") . "','0')";

Although, you don't need it (and should NOT use quotes) for numeric data.

 

 

I'm sorry... I haven't really been through MySQL and thought it was the same...

[Christos]

Thanks for the help.

The mysql_real_escape_string is not working, i think i'm doing something wrong but the mysql_escape_string is working. Is it safe to keep it or is it better to use the str_replace ? I have problem only with the "product_name" field.

$q .= "('$ass_orderID','','$product->virtuemart_manufacturer_id','','$product->virtuemart_product_id','".mysql_escape_string($product->product_name)."','$product->product_price','$product->dl_ebook_url','".gmdate("Y-m-d H:i:s")."','0')";
					$queryflag ++;

Edited April 30, 2013 by Christos

[DavidAM]

1) Define "not working" -- What exactly is it doing that it should not do, or what is it not doing that it should do? Have you printed (or echoed) the ultimate query statement before attempting to execute it, to see what it says and what might be wrong?

 

2) You did not show the code that is actually executing the query. Are you using mysql_query or are you using mysqli_query or are you using PDO, or something else? You cannot mix driver functions.

 

3) Escaping values is important for two reasons: 1) It prevents characters that are "special" to the database engine (such as a single-quote) from being interpreted as the special character, so it is treated as data; and 2) it prevents "special" characters from being injected into the query from user-supplied values - without the escaping, it is possible for a malicious user to manipulate the query. "I only need it for ...", yeah, I've thought that to, and then three weeks later tried to insert data in another field that was not escaped. The script crashed and I had to fix it. It WILL NOT HURT to use it on ALL STRING DATA, so there is NO REASON to NOT USE IT.

 

4) Well, actually this should be #1, but I'm too lazy to renumber. Turn on error reporting. The only reason mysql_real_escape_string() should fail when mysql_escape_string() works is that you don't have a mysql connection to the database (see #2). mysql_escape_string is deprecated and should not be used.

 

4a) When the query fails, you need to echo mysql_error() (or whatever the error message function is for whatever driver you are using). It will tell you what the database said is wrong. This is DIFFERENT and SEPARATE from PHP error reporting.