how to replace apostrophe (’) with single quote(')

Question

It seems there are two types of apostrophes.I want to know the difference between this character(’) and this(')

the first one was copied from Microsoft Word and I'm trying to past it into text area and then insert into database but it doesn't work. it breaks my query so I want to replace it with this one(') please how do I achieve this.

I tried this but it seem not working

function replace_microsolft_apostrophe($string){
        $string = str_replace('’', "'", $string);
        return $string;
    }

my query looks like this:

function create_note($page_id,$title,$note,$category,$author_id){
    global $conn,$notes_table,$pages_table,$notification_table;
    $sql = "INSERT INTO $notes_table(page_id,title,content,note_category) VALUES(?,?,?,?)";
    $query = $conn->prepare($sql);
    $query->bind_param("issi",$page_id,$title,htmlspecialchars($note),$category);
    $query->execute();
}

The above query works fine without this character (’)

also I want to know why it this so? because it is the same key i press but when it goes to Microsoft Word the character seem to change. The reason why am trying to make it work is because a user may copy an already typed work from Microsolf word and past on my application where I expect them to write and note and publish it.

Any help would be much good.

If it breaks your query, it sounds like you are vulnerable to SQL injection. You should use parameterized queries with placeholders, which very likely would fix your issue. What API are you using? — Qirel, Mar 21 '17 at 16:25
Difference between the two: `’` is a right single quotation mark and `'` is an apostrophe. — Tom Udding, Mar 21 '17 at 16:27
I am using parameterised queries... VALUES(?,?,?,?) ...well it doesnt break my code but because of that type of apostrophe a column is inserted with empty string — james Oduro, Mar 21 '17 at 16:27
okay...so how do I replace the right single quotation mark with apostrophe ...if possible how do I maintain it?? — james Oduro, Mar 21 '17 at 16:29
ok but the query works fine without that kind of quotation mark inside my string — james Oduro, Mar 21 '17 at 16:30
`replace_microsolft_apostrophe` works for me, it replaces all right quotation marks. — Tom Udding, Mar 21 '17 at 16:30
`replace_microsolft_apostrophe` - just wondering if that's a typo here? and if it has a bearing on this, should have probably been `microsoft` ? IMHO, the question's unclear. `var_dump` your code/query and see what comes of it. — Funk Forty Niner, Mar 21 '17 at 16:32
plus, escaping the query may be getting rid of the apostrophes altogether, seeing that it's a possible threat/injection. Try adding/using `addslashes()`. — Funk Forty Niner, Mar 21 '17 at 16:33
What character set are you using in your database - it *shouldn't* have any issues with the `’` character... there mere fact that we're posting it in SO comments shows that it *can* go into a database as it is. — CD001, Mar 21 '17 at 16:33
am using this character : example She joined Women’s Fellowship — james Oduro, Mar 21 '17 at 16:34
No... the character **set** - or more correctly the table collation I suppose, `ISO-8859-1 Swedish` (default for MySQL back in ye olde days) or `UTF-8` or what? — CD001, Mar 21 '17 at 16:35
`htmlspecialchars($note)` not entirely sure you can do that in the bind, as it could trigger a "cannot pass by reference" error. Try doing `$note = htmlspecialchars($note);` then doing `("issi",$page_id,$title, $note, $category)`. - check for errors on the query with `mysqli_error($conn)` to see if anything comes of it. and make sure the column types are correct and long enough. — Funk Forty Niner, Mar 21 '17 at 16:40
and if "category" is a string, then `("issi"` should be changed to `("isss"` — Funk Forty Niner, Mar 21 '17 at 16:43
even without the referencing it worked great but if you include this character from microsoft word then you will see empty string has been insert into a column that is bind to that string coming from the user. — james Oduro, Mar 21 '17 at 16:43
make sure that column's length is long enough to hold the data going in then. It could be because of that and including a few comments I left just above. — Funk Forty Niner, Mar 21 '17 at 16:44
please category is not a string ...it is rather an int value — james Oduro, Mar 21 '17 at 16:44
no it is not the length... you try inserting only that this character: ’ — james Oduro, Mar 21 '17 at 16:46
You'll probably find this useful : http://stackoverflow.com/questions/279170/utf-8-all-the-way-through/279279 ... I'm guessing your application is all utf-8 whilst the database is iso-8859-1 (latin1_swedish is 8859-1 by any other name) so when you enter `’` in the textarea you end up with something else in the database (as there are twice as many bits in a utf-8 character than a latin one) which could cause squiffyness. — CD001, Mar 21 '17 at 16:47
@jamesOduro something just dawned on me; get rid of `htmlspecialchars()`. - that's only for display purposes and not to be used when doing an sql operation and is most likely converting things that shouldn't have. — Funk Forty Niner, Mar 21 '17 at 16:53
as per the manual http://php.net/manual/en/function.htmlspecialchars.php under "Performed translations" - *"`'`(single quote) ' (for ENT_HTML401) or ' (for ENT_XML1, ENT_XHTML or ENT_HTML5), but only when ENT_QUOTES is set"* @jamesOduro - ping me back (as I did here) if what I said solved this. — Funk Forty Niner, Mar 21 '17 at 17:00
I guess they haven't then ^ and you haven't responded to both comments. Well, I'll have to move on now, good luck James. — Funk Forty Niner, Mar 21 '17 at 17:14
can you create textarea and trying to insert that character using parameterized query — james Oduro, Mar 21 '17 at 17:17
@jamesOduro by not commenting and not pinging me here as I did for you here with the @ symbol; I don't know that. Given the (new) answer below that didn't work, you should provide us with a minimum [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) - The question lacks the code for the way it's inputted, an example of the source it's coming from and how it's all put together along with the db schema. I'd like to help you out more with this, but I can't. I sincerely wish you well with this James, really. — Funk Forty Niner, Mar 21 '17 at 17:20

score 8 · Accepted Answer · edited Jan 31 '19 at 16:25

WOW! after surfing the net I found Pascal Martin's answer very useful...it also has a reference to a website with the full answer. How to replace Microsoft-encoded quotes in PHP

I was able to replace right quotation mark with normal quote

  //convert single-byte apostrophes -encoded
function convert_smart_quotes($string) 

{ 
    $search = array(chr(145), 
                    chr(146), 
                    chr(147), 
                    chr(148), 
                    chr(151)); 

    $replace = array("'", 
                     "'", 
                     '"', 
                     '"', 
                     '-'); 

    return str_replace($search, $replace, $string); 
}

Also this answer is much more useful: Converting Microsoft Word special characters with PHP

You should also consider the [alternate answer](https://stackoverflow.com/a/6610752/597034) to the first question linked to above, provided by Justin Dominic. It provides a more concise solution (and it has more votes than the accepted answer there), though read the [iconv](https://www.php.net/manual/en/function.iconv.php) documentation also before adopting it. — John Rix, Sep 17 '19 at 12:22

DrKey · Answer 2 · 2017-03-21T17:11:09.057

2

This seems to be a charset issue. Have you checked if even your editor's charset is set to ISO-8859-1?

Anyway, a workaround could be convert your string to hex and let MySQL convert it again to string. I haven't tested this code, but it should work.

function create_note($page_id,$title,$note,$category,$author_id){
    global $conn,$notes_table,$pages_table,$notification_table;
    $sql = "INSERT INTO $notes_table(page_id,title,content,note_category) VALUES(?,?,UNHEX(?),?)";
    $query = $conn->prepare($sql);
    $query->bind_param("issi",$page_id,$title,bin2hex(htmlspecialchars($note)),$category);
    $query->execute();
}

edited Mar 21 '17 at 17:11

answered Mar 21 '17 at 16:57

DrKey

3,365
2
29
46

didn't work for me. this character(’) is giving me stress – james Oduro Mar 21 '17 at 17:11
@jamesOduro if you `echo htmlspecialchars($note)` do you see something wrong in the string? – DrKey Mar 21 '17 at 17:17
yes something like question mark inside the string....that means where that character is found – james Oduro Mar 21 '17 at 17:19
@jamesOduro have you checked if your editor's charset is set to ISO-8859-1? – DrKey Mar 21 '17 at 17:20
something like this:� – james Oduro Mar 21 '17 at 17:21
what is editors charset? you my my text editor that am using? or Meta charset?? – james Oduro Mar 21 '17 at 17:22
@jamesOduro let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138663/discussion-between-drkey-and-james-oduro). – DrKey Mar 21 '17 at 17:28

how to replace apostrophe (’) with single quote(')

2 Answers2

Linked