-2
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "UPDATE table_name SET name=:name where id=1";

    $stmt = $conn->prepare($sql);
    $stmt->execute(['name'=>'dsf"fsd"fds']);

}catch(PDOException $e){
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

with this code name is saved as dsf&quote;fsd&quote;fds in mysql database

with laravel if I save name it will save as dsf"fsd"fds only.

why php saves it differently and how to save it as it is using php pdo. please share the code.

u_mulder
  • 54,101
  • 5
  • 48
  • 64
Sunil Kumar
  • 759
  • 7
  • 17
  • 2
    There's nothing in the code here that would save it with escaped characters. Show us your actual code. – Qirel Jan 13 '20 at 07:31
  • please elaborate. I have presented here with my observation. i don't what are escaped charectors. did you mean " is changed to &quote;? – Sunil Kumar Jan 13 '20 at 07:35
  • 1
    I'm saying that in the code you posted in your question here, there's *nothing* that would escape `"` into `&quote;`. You're not applying `htmlspecialchars()` or any other function to the data, so it is untouched. Show your actual code. – Qirel Jan 13 '20 at 07:36
  • That said, if you're using laravel, why don't you update the values through the model instead..? – Qirel Jan 13 '20 at 07:36
  • yes i am not applying htmlspecialchars() but still " is changing to &quote; automatically why this happens? I am ok with the behaviour of laravel. i want to use php but i want to save " as " only. – Sunil Kumar Jan 13 '20 at 07:40
  • If you're using Laravel, then you should stick with using Laravel's methods for updating the database, either through a model or by using the `DB` class. I recommend that you don't use plain PHP unless you have a very good reason to (which doesn't seem like the case here). – Qirel Jan 13 '20 at 07:50
  • I want to use php to update database. I am asking about the problem not alternative options to go with laravel – Sunil Kumar Jan 13 '20 at 09:41

2 Answers2

0

It was my mistake

actually there was no problem with code. this code doesn't add &quote; to sql database.

I had a function to format the input to sql query

public function test_input($data) {
      $data = trim($data);
      $data = stripslashes($data);
      $data = htmlspecialchars($data);
      return $data;
}

this function had htmlspecialchars function which was converting double quotes into &quote; in database.

Sunil Kumar
  • 759
  • 7
  • 17
-1

As a matter of fact, escaping required for the SQL strings only. It has nothing to do with PDO, prepared statements, safety and such. Once you are going to put a string literal into query - it must have special characters escaped. But once you aren't - no escaping would be good.

Regarding PDO, you want it not to "escape" but to process placeholders in your query. This is how the whole thing works. Using placeholders you are telling PDO to format corresponding values properly. While such a formatting involves not just escaping but many more different measures.

The above information is taken from this answer.

Just for your information that the following single quote, double quote, null byte and backslash characters are evils for sql query:

', ", \0, \
unclexo
  • 3,691
  • 2
  • 18
  • 26
  • I want to only compare php with laravel behaviour. when laravel save " as " only why php saves " as &quote; in mysql. I want to know this. – Sunil Kumar Jan 13 '20 at 07:48
  • Can anybody explain why this answer gets down vote? Show me the reason if you can. – unclexo Jan 13 '20 at 08:49