If string contains ', replace with \' in SQL query

Question

I'm trying to write a query using a variable passed through a URL, the problem is that a lot of the item names contain characters like ' or +, they weren't being passed properly through the URL, and those special character were being ignored(although I checked and couldn't see that they were reserved) Anyways, to get around this I used PHP function rawurlencode(), and rawurldecode() to pass the value as an encoded string, and on the receiving end I use the function to the decoded string. This works, and gives me the value I need to search for in the database, however when writing me query like so:

$name = rawurldecode($_GET["name"]);

$sql = "SELECT name, address, phone_number, hours_weekday, hours_weekend, description, email, food, pool, tv, dancing, late
        FROM pubs WHERE name LIKE '" . $name . "'";

If the name was something like Darby's, the ' after the why acts as the end of the string in the statement, so what I am looking to do, is IF the string contains character ' then replace it with \' to escape it. Does anyone know how to do this or if it's possible?

Thank you!

@NiettheDarkAbsol I will be using prepared statements and binding parameters, I'm just currently testing a few things locally — coopwatts, Nov 09 '15 at 19:27
To add to Niet's comment (for the benefit of future readers), the reason you don't want to do this is SQL Injection attacks. Suppose a nefarious user on your site typed in `;drop table pubs;--` as his name. Results wouldn't be pretty. — John Chrysostom, Nov 09 '15 at 19:29
E-V-E-R. Trust me, even when testing a few things locally, it's always best to adhere to standards of security ;) — Niet the Dark Absol, Nov 09 '15 at 19:42

score 0 · Accepted Answer · answered Nov 09 '15 at 19:24

0

Assuming your mysqli connection is called $mysqli, then

$name = $mysqli->real_escape_string($name);

before adding $name to your SQL query string.

answered Nov 09 '15 at 19:24

wogsland

9,106
19
57
93

If string contains ', replace with \' in SQL query

1 Answers1