Retrieving mysqli_real_escape_string from MySQL

Question

// inserting into DB from form

if (!empty($_POST['Title'])) {
    $t = mysqli_real_escape_string($dbc, ($_POST['Title']));
} else {
    $errors[] = 'Please enter a Headline title';
}

// Add the news to the database:

$q = 'INSERT INTO news ( Title, Content) VALUES (?, ?)';
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'ss', $t, $c);
mysqli_stmt_execute($stmt);

If the $t contains an apostrophe e.g

We're awesome

it will add it to the DB as

"we\'re awesome"

later on:

How do I then run a query where title = $t

$q = "SELECT * FROM news WHERE title= '$t'";

The query fails because apostrophe cuts the SQL off effectively

SELECT * FROM news WHERE title = ' we're awesome

Are you sure in what you are talking? :) There's some missconceptions. You told us it's inserting `we\'re awesome` so the statement has to be `SELECT * FROM news WHERE title = 'we\'re awesome'`. Isn't it? P.S.: Why not: `$q = "SELECT * FROM news WHERE title = ?"`; `$stmt = mysqli_prepare($dbc, $q); mysqli_stmt_bind_param($stmt, 's', $row['title']); mysqli_stmt_execute($stmt);` — Royal Bg, Apr 15 '13 at 16:51

score 2 · Accepted Answer · answered Apr 15 '13 at 16:57

2

You can either escape or use prepared statements, but not both!

answered Apr 15 '13 at 16:57

Álvaro González

142,137
41
261
360

ah okay, so by using the prepared statement it already sanitises any user input? do you recommend removing the escape – FullStack Apr 15 '13 at 17:40
Sure. Prepared statements are the state of the art and easy to use alternative to escaping. See also the Stack Overflow classic [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). – Álvaro González Apr 15 '13 at 18:44

score 0 · Answer 2 · answered Apr 15 '13 at 16:54

0

You use mysqli_real_escape_string on your search again, like you did with the insert.

$t = mysqli_real_escape_string($link,$t);
$q = "SELECT * FROM news WHERE title= '$t'";

That's it.

answered Apr 15 '13 at 16:54

dmgig

4,400
5
36
47

Retrieving mysqli_real_escape_string from MySQL

2 Answers2