Double quotes getting removed in query

HI everyone,

I am having problems with inserting or updating double quotes in my database, every time i update or insert a new name for a product i will remove the double quotes, but for some reason single quotes work fine.

my setup
php 5.3.28
magic_quotes off

Code i use
this is a simple update statement

$queryStr = "UPDATE products SET `name`='".mysqli_real_escape_string($link, $name)."' WHERE product_id='$productID'"

i have tried without mysqli_real_escape_string() but still no luck, is there anything that i am doing wrong?
if anyone needs more details please let me know :smile:

The problem arises because you’re using double quotes to write your sql string.

Try to use str_replace and change the double qoutes for ".

That should escape them

edit: is $productID a number? If $productID is a number and the filed product_id is a number too, you don’t need single quotes there

Hi molona

Thanks escaping the quote works.
So ever time i need to display the name on the front-end i have to remove the \ from the name? or is there a why to remove them in a query?
And yes the product is a number, thanks for the note.

It would be safer to use Prepared Statements for that as that frees you of any quote issues.

// using PDO as example here because I’m more familiar with that than with MySQLi
$stmt = $pdo->prepare('UPDATE products SET `name` = ? WHERE product_id = ?;');
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $productID, PDO::PARAM_INT); // assuming integer ID here
$stmt->execute();

@Dormilich is right. Prepared statements is safer.

But just for the sake of learning, let’s go with this.

I’m not sure what you mean.

The way your creating your query, it looks like the double quote is part of the name itself.

Let me put an example.

Let’s say that I sell TVs and I want to update the name of the product 823.

Product 823’s description is Samsung Smart TV but I want to change it to Samsung Smart 3D TV

So my query (not using variables now) would be

UPDATE products SET 'name'='Samsung Smart 3D TV' WHERE product_id=823"

See that I’m using single quotes around the new name.

The way you’re doing it is

UPDATE products SET 'name'='\"Samsung Smart 3D TV\"' WHERE product_id=823"

That means that the text that will be saved as the new name will be “Samsung Smart 3D TV”, including the double quotes

Hi

No using “. and .” is for appending in php so the final result is name=‘the new name’ not name=‘“the new name”’

You use append with a . example $name = 'foo'; $string = "the name is ".$name." now";
if you echo that you will have (the name is foo now)

I will have a look into prepared statements at a latter time :smile:
Thanks for all the help so far.

Hi @generalProf. I don’t fully understand the issue you’re having - would you be able to explain it further? Are you wanting to have double quotes in a products name, but inserting this name into the DB removes them? I’m a little confused because in your reply here, you’re asking how to display the data, not insert it.

Yes, prepared statements are the way to go. Though I’d advise you not to use a SQL-specific syntax in your query because PDO is a database abstraction layer; not a query abstraction layer. Thus, by writing SQL-dependent code, you’re effectively losing the advantages brought by the abstraction layer (ability to change underlying database somewhat seamlessly through the DSN), whilst still keeping the disadvantages, like slower performance. And in this case name, whilst not very descriptive, is not a reserved word in MySQL.

Here’s an example of using prepared statements using the mysqli extension (with the procedural interface to match OPs DB access style):

$queryStr = mysqli_prepare($link, 'UPDATE products SET name = ? WHERE product_id = ?');
mysqli_stmt_bind_param($queryStr, 'si', $name, $productID);
mysqli_stmt_execute($queryStr);

Hi @tpunt to explain my problem and that replay:

My problem was when i inserted names into my database it removed double quotes because i did not escape the quote, thanks to @Dormilich that problem is fixed by escape the double quote.

In that replay i was asking if there is a way to remove the backslashes from the names in the query itself or do i need to str_replace every time i have to display the product name.

Hope that clears up the confusion for you :smile:
Have a great day.

My source of confusion is that you aren’t able to insert double quotes into your database without them being removed. The mysqli_real_escape_string() function should work fine for this task, but in your scenario, you are having to resort to manually escaping them with str_replace(). That’s what I don’t understand. Would you be able to give us anymore context to your problem, like how $name is being validated above? And can you also execute the following line and tell me the output:

var_dump(get_magic_quotes_gpc());

The name is passed by POST data $name = $_POST['basicInfo-name'] and for the magic_qoutes it returns int(0) so that means it’s off right?
When i got the problem i was surprised because mysqli_real_escape_string() should escape :wink: the double quote for me.

To be more clear about the $name what happens is when the form is submitted i get the form data and then i assign the $_POST['basicInfo-name'] to $name variable and then run the query which was the mysqli_real_escape_string() function. I hope that help

Could you please show me an example product name you’re trying to insert into your database, and then show me that same product name as it is stored in your database? (You may need to apply the htmlentities() function upon the output.)

Hi @tpunt example of a name will be LG G123 LED 27" and in the database it is LG G123 LED 27
I have not tried htmlentities() will have a look thanks.

EDIT: but when i escape it it would be LG G123 LED 27\"

I stil haven’t really had any further thoughts on this. Perhaps it would be easier if you screen-shared to me in case there’s anything else that may be effecting your code?

I’d like to know what the following outputs though:

var_dump($name);
var_dump(mysqli_real_escape_string($link, $name));

Sorry for the late reply i have a lot to do - but still thanks for tying to help here is that var_dumps

The name without mysqli_real_escape_string()
string(15) "samsung 27" LED"
And with
string(16) "samsung 27\" LED"

So it looks like there is no problem with escaping, can’t it be something with the database itself?

How are you trying to retrieve and/or verify that there is no quote in the database? The problem may be on that end instead of the insertion.