Stripping single quotes from user input in seach bar

Question

I have a simple PHP/MySQL psychiatric drug database website. Everything is working well, until the user puts a single quote anywhere into the search string. I get this error:

Fatal error: Uncaught Error: Call to a member function fetch() on boolean in /home/psychoph/public_html/connection.php:76 Stack trace: #0 /home/psychoph/public_html/index.php(70): include() #1 {main} thrown in /home/psychoph/public_html/connection.php on line 76

I tried this: $generic = $dbc->real_escape_string($generic); to no avail ($generic is the variable assigned to the user input)...I get this error:

Fatal error: Uncaught Error: Call to undefined method PDO::real_escape_string() in /home/psychoph/public_html/connection.php:12 Stack trace: #0 /home/psychoph/public_html/index.php(70): include() #1 {main} thrown in /home/psychoph/public_html/connection.php on line 12

Any guidance would be appreciated. I really just need to strip single quotes from the user input, but don't know how.

Here's the code for connection.php:

<?php
$dbc = new PDO(
"mysql:host=173.236.101.74;dbname=psychoph_psychopharm",
'psychoph_knasky',
'#######');

try {
$results = $dbc->query("
    SELECT * 
    FROM drugs 
    WHERE generic = '$generic'
    OR brand = '$generic'
    "); // this method call returns the query results and places it into $results
} catch (Exception $e) {
echo "Unable to retrieve results";
    exit;
}

try {
$side_effects = $dbc->query("
    SELECT se.side_effect 
    FROM side_effects AS se
    JOIN jnct_drug_side_effects AS jse ON se.id = jse.side_effect 
    JOIN drugs AS d ON jse.drug = d.id 
    WHERE generic = '$generic'
    OR brand = '$generic'
    ");
} catch (Exception $e) {
echo "Unable to retrieve results";
    exit;
}

try {
$indications = $dbc->query("
    SELECT i.indication 
    FROM indications AS i
    JOIN jnct_drug_indications AS ji ON i.id = ji.indication 
    JOIN drugs AS d ON ji.drug = d.id 
    WHERE generic = '$generic'
    OR brand = '$generic'
    ");
} catch (Exception $e) {
echo "Unable to retrieve results";
    exit;
}

try {
$oi_indications = $dbc->query("
    SELECT oi.indication 
    FROM ol_indications AS oi
    JOIN jnct_ol_drug_indications AS joi ON oi.id = joi.indication 
    JOIN drugs AS d ON joi.drug = d.id 
    WHERE generic = '$generic'
    OR brand = '$generic'
    ");
} catch (Exception $e) {
echo "Unable to retrieve results";
    exit;
}

$row = $results->fetch(PDO::FETCH_ASSOC); 

$se_row = $side_effects->fetchAll(PDO::FETCH_COLUMN, 0); 
$print_se = implode(", ", $se_row); 

$ind_row = $indications->fetchAll(PDO::FETCH_COLUMN, 0); 
$print_ind = implode(", ", $ind_row); 

$oi_ind_row = $oi_indications->fetchAll(PDO::FETCH_COLUMN, 0); 
$print_ol_ind = implode(", ", $oi_ind_row); 
?>

To clarify my question, I don't have any SQL injection concerns. This isn't a site that contains any data that needs protection (unless someone really wants to change the half-life of Zoloft on some random website for psychiatrists). I'm just trying to get this to work. I'm a physician trying to build a useful web tool, and don't like that a single quotation mark in my query returns an error message to the user. Is there a simple fix to this? I'm not a programmer - I'm trying to learn just enough to make this site work. Thanks, all!

Can you add the code here that's related to the calls in question, no just the errors? Are you using prepared statements with placeholder values? If not, that *will* fix this sort of problem. Stripping does not fix the problem, it just hides a bigger one. — tadman, Apr 03 '17 at 00:50
`real_escape_string` is a `mysqli` function. Use parameterized queries, it will be safer. — chris85, Apr 03 '17 at 01:10
The line 76 mentioned was simple the `$row = $results->fetch(PDO::FETCH_ASSOC);` — Kevin Michael, Apr 03 '17 at 01:18
The line 12 error was '$generic = $dbc->real_escape_string($generic);' that I tried. — Kevin Michael, Apr 03 '17 at 01:18
So I'm just learning I can't drop too much code into the comments. So, this connection.php page starts with the db connection: ‘$dbc = new PDO("mysql:host=173.236.101.74;dbname=psychoph_psychopharm",'psychoph_knasky’,’#######’);’ Followed by a bunch of try/catch queries, and then ends with a bunch of fetch and fetchALL PDO statements. Then the main page spits out the results, etc. Are there any specific lines of code that would help diagnosis this problem? There's a pretty severe character count limit on these comments, much to my dismay. — Kevin Michael, Apr 03 '17 at 01:22
Yes, the `fetch` fails because the query is invalid. The `'` breaks the query. The `real_escape` is not a `PDO` function so it is undefined. Use parameterized queries and get rid of the escaping (the parameterized query will do that for you). Code is unreadable in comments. To add more code use the `edit` option under the `tags` of your question. http://stackoverflow.com/posts/43175091/edit Parameterizing should fix everything though.. — chris85, Apr 03 '17 at 01:23

score -1 · Answer 1 · edited May 23 '17 at 11:46

-1

You could replace it?

http://php.net/manual/en/function.str-replace.php

You could replace with ''. <~~ essentially a blank string variable

This link should help.

Regular Expression Sanitize (PHP)

edited May 23 '17 at 11:46

Community

1
1

answered Apr 03 '17 at 01:10

addohm

2,248
3
14
40

1

This is a bad approach. OP should use parameterized queries. – chris85 Apr 03 '17 at 01:12
Out of desperation, I tried the str-replace thing, which 'worked,' but it screwed up my main page because my homepage only shows if that search terms is `!isset`, and apparently when I use str-replace on `$generic`, it ends up becoming set (I was replacing with ""). – Kevin Michael Apr 03 '17 at 01:26

Stripping single quotes from user input in seach bar

1 Answers1