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
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();
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);
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
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:
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 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.)
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: