SQL - Database search gives error when user form input search criteria contains apostrophe

Question

I have a database of mountains the names of which often contain an apostrophe, e.g. Beinn A'Chroin. All my search criteria work fine except when the user inputs a search via the form with the apostrophe included, i.e. Beinn A'Chroin, and then it throws up an error - Beinn A will work, Chroin will work, but never with the apostrophe. As most users will invariably insert the proper name, including the apostrophe I would prefer always to have it in the table data - Your help appreciated - Thanks John

The relative portion of my code is:

$srch = $_POST['srch'];


// Prepare  query 

// Named Mountain Search Search for:



if ($srch != '') { //Options for specific walk types in a specific area selected

    $query = "SELECT walk, status, distance, report, dateofwalk FROM walkslist WHERE walk LIKE '%$srch%' ORDER BY walk ASC;";

Try replacing the user's apostrophe with two apostrophes. Not sure of mySQL, but some SQL servers handle apostrophes that way — Sparky
– Sparky, Commented Dec 1, 2015 at 11:43

Abhishek Sharma · Accepted Answer · 2015-12-01 11:40:13Z

0

update your code use PDO

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    $sql = 'SELECT walk, status, distance, report, dateofwalk 
FROM walkslist WHERE walk LIKE ? ORDER BY walk ASC;';

    $q = $conn->prepare($sql);
    $q->execute(array('%$srch%'));
    $q->setFetchMode(PDO::FETCH_ASSOC);

    while ($r = $q->fetch()) {

    }
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}

edited Dec 1, 2015 at 11:40

answered Dec 1, 2015 at 5:05

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JMH Over a year ago

Hi Rahautos Thanks for your reply, however when I use the escape string as you suggest I get the following error:

JMH Over a year ago

Hi Rahautos Thanks for your reply, however when I test the escape string as you suggest I get the following error: – Deprecated: mysql_real_escape_string(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in C:\wamp\www\Walkslog\dbwalks\srchforA.php on line 46 Any suggestions - Thanks John Call Stack

Collectives™ on Stack Overflow

SQL - Database search gives error when user form input search criteria contains apostrophe

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related