You should be escaping each of these strings (in both snippets) with mysql_real_escape_string().

https://www.php.net/mysql-real-escape-string

The reason your two queries are behaving differently is likely because you have magic_quotes_gpc turned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., "O'Brien" -> "O\'Brien").

Once you store the data, and subsequently retrieve it again, the string you get back from the database will not be automatically escaped for you. You'll get back "O'Brien". So, you will need to pass it through mysql_real_escape_string().

Put quite simply:

SELECT 'This is Ashok''s Pen.';

So inside the string, replace each single quote with two of them.

Or:

SELECT 'This is Ashok\'s Pen.'

Escape it =)

The MySQL documentation you cite actually says a little bit more than you mention. It also says,

A “'” inside a string quoted with “'” may be written as “''”.

(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequences looks pretty similar.)

I think the Postgres note on the backslash_quote (string) parameter is informative:

This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks...

That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.

Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.

The answer is that you don't need to. The proper way to use PDO's prepare is like this:

$stmt = $pdo->prepare(
   "SELECT * FROM `products_keywords` WHERE `product_type` = ?");

This is the whole point of using a prepared statement. Then you bind the parameter as follows:

$stmt->bindParam(1, $product_type)

Proof,

Schema:

create table `products_keywords`
(   `id` int not null,
    `products_keywords` varchar(1000) not null,
    `product_type` varchar(100) not null
);
insert `products_keywords` (`id`,`products_keywords`,`product_type`) values  
(1,'zoom lawn cut mower',"Lawn Mower"),
(2,'stylish torso Polo','Men\'s Shirt');

View data:

select * from `products_keywords`;
+----+---------------------+--------------+
| id | products_keywords   | product_type |
+----+---------------------+--------------+
|  1 | zoom lawn cut mower | Lawn Mower   |
|  2 | stylish torso Polo  | Men's Shirt  |
+----+---------------------+--------------+

PHP:

<?php
    // turn on error reporting, or wonder why nothing is happening at times
    error_reporting(E_ALL);
    ini_set("display_errors", 1);    

    $servername="localhost";
    $dbname="so_gibberish";
    $username="nate123";
    $password="openSesame1";

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

        $product_type="Men's Shirt";
        $stmt = $pdo->prepare("SELECT * FROM `products_keywords` WHERE `product_type` = ?");
        $stmt->bindParam(1, $product_type);
        $stmt->execute();
        while(stmt->fetch()) {
            echo row['products_keywords'].", ".$row['product_type']."<br/>";
        }
    } catch (PDOException $e) {
        echo 'pdo problemo: ' . $e->getMessage();   // dev not production code
        exit();
    }
?>

Browser:

First of all you need to stop using mysql_* Functions because it's omitted on php 7.0 instead use mysqli_* or PDO

you can add back slashes to escape single and double quotes using addslashes() or since you are inserting into database you need to use mysql_real_escape_string()

mysql_real_escape_string($import);

addslashes() will escape single quotes with a leading backslash which is valid syntax in MySQL but not in MS SQL Server. The correct way to escape a single quote in MS SQL Server is with another single quote. Use mysql_real_escape_string() for MySQL (mysql_escape_string() has been deprecated). Unfortunately, no analogous mssql_ function exists so you'll have to roll your own using str_replace(), preg_replace() or something similar. Better yet, use a database neutral abstraction layer such as PDO that supports parameterized queries.

if(get_magic_quotes_gpc())
{
    $string = stripslashes($string);
            $string = mysqli_real_escape_string($string);
}
else
{
    $string = mysqli_real_escape_string($string);
}

You might want to make a function out of this

Insert string with single quote(') or double quote(") in mysql

Just Use addslashes(); in Insertion and stripslashes(); for fetch data.

$str = "Hello Friend's.. Hows you all"s.";
// Outputs: Hello Friend\'s..Hows you all\"s.
echo addslashes($str);

stripslashes — Un-quote string quoted with addslashes(). Returns a string with backslashes stripped off. (\' becomes ' and so on.) Double backslashes (\\) are made into a single backslash (\).

$str = "Hello Friend\'s.. Hows you all"s."; // Outputs: Hello Friend's.. Hows you all"s.
echo stripslashes($str);

Now we come to the point. If we insert string into database with single or double quote like this:

$str = “Hello Friend's.. Hows you all"s.”;
$query = “INSERT INTO tbl (description) VALUES (‘$str’)”;

This will occur error, but if we use addslashes($str) function like below and then insert into database, then no error will be occurred.

$str = “Hello Friend's.. Hows you all"s.”;
$desc_str = addslashes($str);
$query = “INSERT INTO tbl (description) VALUES (‘$desc_str’)”;

similarly we can use stripslashes($str) to print that table field value like this:

echo stripslashes($str);

It sounds like Magic Quotes are enabled in your PHP configuration.

To check if it's actually enabled:

echo get_magic_quotes_gpc();

To disable, edit your php.ini file:

; Magic quotes
;

; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = Off

; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.
magic_quotes_runtime = Off

; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off

Or add this line to your .htaccess:

php_flag magic_quotes_gpc Off

If you are just replacing ' with '' then you could exploit this by injecting a \' which will turn into a \'' and this will allow you to break out because this gives you a "character literal" single-quote and a real single-quote. However, the replacement of "\\" with "\\\\" negates this attack. The double-single-quote is used to "escape" single quotes for MS-SQL, but this isn't proper for MySQL, but it can work.

The following codes proves that this escape function is safe for all except three conditions. This code permutes though all possible variations of control charters, and testing each one to make sure an error doesn't occur with a single quote encased select statement. This code was tested on MySQL 5.1.41.

<?php
mysql_connect("localhost",'root','');
function escape($value) {

  $value = str_replace("'","''",$value);
  $value = str_replace("\\","\\\\",$value);
  return $value;

}

$chars=array("'","\\","\0","a");

for($w=0;$w<4;$w++){
    for($x=0;$x<4;$x++){
        for($y=0;$y<4;$y++){
            for($z=0;$z<4;$z++){
                mysql_query("select '".escape($chars[$w].$chars[$x].$chars[$y].$chars[$z])."'") or die("!!!! $w $x $y $z ".mysql_error());
            }       
        }
    }
}
print "Escape function is safe :(";
?>

Vulnerable Condition 1: no quote marks used.

mysql_query("select username from users where id=".escape($_GET['id']));

Exploit:

http://localhost/sqli_test.php?id=union select "<?php eval($_GET[e]);?>" into outfile "/var/www/backdoor.php"

Vulnerable Condition 2: double quote marks used

mysql_query("select username from users where id=\"".escape($_GET['id'])."\"");

Exploit:

http://localhost/sqli_test.php?id=" union select "<?php eval($_GET[e]);?>" into outfile "/var/www/backdoor.php" -- 1

Vulnerable Condition 2: single quotes are used, however an alternative character set is used..

mysql_set_charset("GBK")
mysql_query("select username from users where id='".escape($_GET['id'])."'");

Exploit:

http://localhost/sqli_test.php?id=%bf%27 union select "<?php eval($_GET[e]);?>" into outfile "/var/www/backdoor.php" -- 1

The conclusion is to always use mysql_real_escape_string() as the escape routine for MySQL. Parameterized query libraries like pdo and adodb always use mysql_real_escape_string() when connected to a mysql database. addslashes() is FAR BETTER of an escape routine because it takes care of vulnerable condition 2. It should be noted that not even mysql_real_escape_string() will stop condition 1, however a parameterized query library will.