1

I want know if there is a function in MySQLi that will let me to look for a words that contain a single quote.

An example is better than hundren explaination, here is it:

-> First I receive a word to search in PHP (Ex: Example's containing quote) I have a function that remove all quote (') from any received text string And then i perform a search in the MySQL database, but the value in the MySQL database contain the QUOTE.

So, i receive the data like this:

$text_to_search = "Examples containing quote"; // Removed the quote
Column in database = "Example's containing quote";

How to remove the quote in database so i can compare it to the received text string with quote removed ?

Do SOUNDEX will work in my case ?

Thank's in advance.

Matii
  • 21
  • 1
  • 1
    If you want to match the string in DB which contains `'` then why remove it? – Dharman Dec 15 '18 at 19:37
  • 1
    @Dharman Well, maybe it wasn't removed and the user input just excluded it. How many times do you add a single quote when searching things in google for instance? :) Seems a legit question... but the scope of an answer is pretty broad on multiple ways to approach it. – IncredibleHat Dec 15 '18 at 19:38
  • 1
    The OP says he's got a function to remove all single quotes. I agree the question is legit, and I am not voting to close, but I would like to learn more details about the context in which this question arose. – Dharman Dec 15 '18 at 19:40
  • I remove it from all received string to prevent from SQLi, i think SOUNDEX solved the problem, you can check it from here: http://sqlfiddle.com/#!9/474c92/4, notice that it's a second layer of security, i already using prepared statments etc ... – Matii Dec 15 '18 at 19:47
  • 1
    This is not how your prevent SQLi. To prevent SQLi you need to use Prepared statements. [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Dharman Dec 15 '18 at 19:52
  • If you use prepared statements then removing quotes will just break your data. It will not have any further effect on the SQL since the statement is already prepared. – Dharman Dec 15 '18 at 19:53
  • What if the statement is not prepared, what is the approach to take in that case ? – Matii Dec 15 '18 at 20:03

1 Answers1

0

You can conditionally check for both versions of your string. Query for one or two values depending on the existence of a single quote.

Untested code:

$config = ['localhost', 'root', '', 'dbname'];
$search = "Examples containing quote";
$values = [$search];
if (strpos($search, "'") !== false) {
    $values[] = str_replace("'", "", $search);
}
$count = sizeof($values);
$placeholders = implode(',', array_fill(0, $count, '?'));
$param_types = str_repeat('s', $count);

if (!$conn = new mysqli(...$config)) {
    echo "MySQL Connection Error: <b>Check config values</b>";  // $conn->connect_error
} elseif (!$stmt = $conn->prepare("SELECT * FROM tablename WHERE columnname IN ($placeholders)")) {
    echo "MySQL Query Syntax Error: <b>Failed to prepare query</b>";  // $conn->error
} elseif (!$stmt->bind_param($param_types, ...$values)) {
    echo "MySQL Query Syntax Error: <b>Failed to bind placeholders and data</b>";  // $stmt->error;
} elseif (!$stmt->execute()) {
    echo "MySQL Query Syntax Error: <b>Execution of prepared statement failed.</b>";  // $stmt->error;
} elseif (!$result = $stmt->get_result()) {
    echo "MySQL Query Syntax Error: <b>Get Result failed.</b>"; // $stmt->error;
} else {
    $resultset = $result->fetch_all(MYSQLI_ASSOC);
    echo "<div>Numrows: " , sizeof($resultset) , "</div>";
    foreach ($resultset as $row) {
        echo "<div>Row: {$row['columnname']}</div>";
    }
}
mickmackusa
  • 43,625
  • 12
  • 83
  • 136