PDO not handling apostrophe in field data even when using prepared statements

Question

I was under the impression that PDO's prepared statements also did what the old MYSQL_REAL_ESCAPE_STRING did.My code uses prepared statemts like this:

 $updatesql = "UPDATE projects
SET title= ?, project_status= ?, bpm= ?, genre= ?, release_genre= ?, 
vocals= ?, lyrics_written= ?, voice_of= ?, start_date= ?, finish_date= ?, 
project_time= ?, file_name= ?, mixed= ?, mastered= ?, mixed_by= ?, 
mastered_by= ?, loudness_level= ?, release_date= ?, artwork_link= ?, 
audio_link= ?, length= ?, lyrics_link= ?, priority= ?, notes= ?, lyrics= ?, 
project_key= ?, featuring= ?, producers= ?  WHERE id= ?
LIMIT 1";



//update record with PDO

$pdo->prepare($updatesql)->execute([$title, $project_status, $bpm, $genre, $release_genre, 
$vocals, $lyrics_written, $voice_of, $start_date, $finish_date, 
$project_time,$file_name, $mixed, $mastered, $mixed_by, 
$mastered_by, $loudness_level, $release_date, $artwork_link, 
$audio_link, $length, $lyrics_link, $priority, $notes, $lyrics, 
$project_key, $featuring, $producers, $id ]);

yet Im finding if a form submits the word O'riely, it causes a MYSQL ERROR.

i CANT USE mysqli_real_escape_string because it expects a mysqli connection as the 1st paramater and Im using PDO.

Is the only other solution to use addslashes to all my DB inserts and then stripslashes on all my display data fields? That seems like something we would only do 10 years ago. What am I missing as it pertains to best practice for handling apostrophe's etc. when using PDO prepared statements?

UPDATE:

My local environment/server info is as follows: Server: Localhost via UNIX socket Server type: MySQL Server version: 5.7.26-0ubuntu0.18.04.1 - (Ubuntu) Protocol version: 10 User: root@localhost Server charset: UTF-8 Unicode (utf8) Apache/2.4.29 (Ubuntu) Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $ PHP extension: mysqliDocumentation mbstringDocumentation PHP version: 7.2.17-0ubuntu0.18.04.1

ERROR MESSAGE:

INSERT INTO projects ( title, project_status, bpm, genre, release_genre, 
vocals, lyrics_written, voice_of, start_date, finish_date, 
project_time, file_name, mixed, mastered, mixed_by, 
mastered_by, loudness_level, release_date, artwork_link, audio_link, 
length, lyrics_link, priority, notes, lyrics, 
project_key,featuring, producers ) 
VALUES ( 'wyatt's', 'Idea Only','99', '99', '99',
'0','0', '', '1969-12-31', '1969-12-31', 
'0', '', '0', '0', '', 
'', '', '1969-12-31', '', '', 
'', '', '3', '', '', 
'','', '')
SQLSTATE[42000]: Syntax error or access violation: 1064 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 's', 'Idea Only','99', '99', '99', '0','0', '', '1969-12-31', '1969-12-31', '0',' at line 7

The ususual "there is an error in your mysql statement near 'riley.. " which indicates mysql rejected the statement syntax right at aposteophe. Ill post actual error in the morning. — Norman Bird
It'd probably be helpful to also post the field name and data type that you are having issues with. — Nick
The query in the error message is not the one of your code. (UPDATE vs INSERT) — Ulrich Thomas Gabor
As @GhostGambler points out, the error is coming from a different query. Perhaps that INSERT is not being prepared? — Nick

Your Common Sense Your Common Sense · Accepted Answer · 2019-05-13T05:38:55

This needs to be addressed.

PDO does handle an apostrophe, as well as any other character in the input data added to the query though a parameter.

The code you posted here will never produce an error like this.

So your problem is elsewhere. You may be running another version of this code, or have some typo in your query or something else as silly. It happens to everyone. But you must be firm with your knowledge. If your query returns an error, then you have to investigate this error, not blame PHP for not doing what it's supposed to do. It usually does. So the problem is on your part and nobody could tell you which one. You can d it only yourself, by diligently debugging your code.

And here you can learn how to do that. I've got an article that helps you to make your question answerable on Stack Overflow, or - most likely - find the problem yourself in the process: How to debug your PDO code

PDO not handling apostrophe in field data even when using prepared statements

1 Answers