Is MySQL_Real_Escape_String() Necessary When Using Prepared Statements

Is mysql_real_escape_string() necessary when using prepared statements?

No, prepared queries (when used properly) will ensure data cannot change your SQL query and provide safe querying. You are using them properly, but you could make just one little change. Because you are using the '?' placeholder, it is easier to pass params through the execute method.

$sql->execute([$consulta]);

Just be careful if you're outputting that to your page, SQL parameter binding does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well when outputting.

PHP Prepared Statement vs mysqli_real_escape_string

An age old argument both PHP Prepared Statement and PHP mysqli_real_escape_string do not perform the same function although both share the same goal of trying to get your string or user input in a format that may not cause harm to your database i used the term may because lack of proper validation will still result to same problems.

take for instance a delete query for users to delete their own post without proper validation user may delete posts of other users and user wont even need an SQL injection to archive that.

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection now this means that mysqli_real_escape_string is dependent on your current character set.

while prepared statements binds your values / user inputs to a specific Data type and instructs your sql to treat it a such but this is also dependent on you specifying the data type.

basically mysqli_real_escape_string will take out the junk in the user input according to a defined character set while prepared statements will make sure if there be any junk in the user input it will be only interpreted as you want it to be interpreted which is usually as user junk :)

what am try to say is using both is not a bad idea but protection of your data base falls to you the developer to take all necessary and logical step to archive maximum security no one cares how fast the hacked site was!!

Should I use mysqli_real_escape_string or should I use prepared statements?

Prepared statements only. Because nowhere escaping is the same thing. In fact, escaping has absolutely nothing to do with whatever injections, and shouldn't be used for protection.

While prepared statements offer the 100% security when applicable.

Do I need to escape my variables if I use MySQLi prepared statements?

No, if you use prepared statements everywhere in your application you are safe from SQL injection. However, an important "gotcha" is 2nd order injection attacks which happen when some queries use prepared statements and others don't.

According to this answer of a similar question on SO:

prepared statements / parameterized queries are sufficient to prevent 1st order injection on that statement. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

In summary, prepared statements create a separation between the data being sent and the SQL query itself, ensuring that the data can not be misinterpreted as the SQL query. However, an attacker can still enter SQL as data, and although it will not be executed when it is first stored if you are using prepared statements, you must still use caution when retrieving said results. Prepared statements protect your application in that particular place, but because SQL is still allowed to be stored in the database, your application is unsafe if you're later using that data without parameterization.

PHP: Is mysql_real_escape_string sufficient for cleaning user input?

mysql_real_escape_string is not sufficient in all situations but it is definitely very good friend. The better solution is using Prepared Statements

//example from http://php.net/manual/en/pdo.prepared-statements.php

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

Also, not to forget HTMLPurifier that can be used to discard any invalid/suspicious characters.

...........

Edit:
Based on the comments below, I need to post this link (I should have done before sorry for creating confusion)

mysql_real_escape_string() versus Prepared Statements

Quoting:

mysql_real_escape_string() prone to
the same kind of issues affecting
addslashes().

Chris Shiflett (Security Expert)

Do I have to use mysql_real_escape_string if I bind parameters?

No, you don't have to escape value yourself (i.e. no you don't need to call mysqli_real_escape_string), when you are using prepared statements : the DB engine will do that itself.

(Actually, if you were calling mysql_real_escape_string and using bound parameters, your strings would get escaped twice -- which would not be great : you'd end up with escaping characters everywhere...)

As a sidenote : your values are passed as integers (as indicated by the 'ii'), so you wouldn't have to call mysql_real_escape_string, even if you were not using prepared statements : as its name indicates, this function is used to escape... strings.

For integers, I generally just use intval to make sure the data I inject into my SQL queries really are integers.

(But, as you are using prepared queries, once again, you don't have to do that kind of escaping yourself)

how to use mysqli_real_escape_string in PHP Prepared Statements?

Prepared queries (when used properly) will ensure data is properly escaped for safe querying so you dont need to use mysqli_real_escape_string at all. You are kind of using them properly, just need change one little thing. Because you are using the '?' placeholder, it is better to pass params through the execute method.

$sql->execute(array($test));

Just be careful if you're outputting that to your page, database sanitization does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well.
Here is a link to this question here for prepared statements and real_escape_string

MySQL prepared statements do I still need to use mysql_escape_string

PDO should take care of the escaping/sanitizing for you. Assuming you mean:

$db->prepare('SELECT * FROM table WHERE foo = ?');
$db->execute(Array("bar's baz"));

I have an array of integers, how do I use each one in a mysql query (in php)?

As with nearly all "How do I do SQL from within PHP" questions - You really should use prepared statements. It's not that hard:

$ids  = array(2, 4, 6, 8);

// prepare an SQL statement with a single parameter placeholder
$sql  = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id = ?";
$stmt = $mysqli->prepare($sql);

// bind a different value to the placeholder with each execution
for ($i = 0; $i < count($ids); $i++)
{
    $stmt->bind_param("i", $ids[$i]);
    $stmt->execute();
    echo "Updated record ID: $id\n";
}

// done
$stmt->close();

Alternatively, you can do it like this:

$ids    = array(2, 4, 6, 8);

// prepare an SQL statement with multiple parameter placeholders
$params = implode(",", array_fill(0, count($ids), "?"));
$sql    = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id IN ($params)";
$stmt   = $mysqli->prepare($sql);

// dynamic call of mysqli_stmt::bind_param                    hard-coded eqivalent
$types = str_repeat("i", count($ids));                        // "iiii"
$args = array_merge(array($types), $ids);                     // ["iiii", 2, 4, 6, 8]
call_user_func_array(array($stmt, 'bind_param'), ref($args)); // $stmt->bind_param("iiii", 2, 4, 6, 8)

// execute the query for all input values in one step
$stmt->execute();

// done
$stmt->close();
echo "Updated record IDs: " . implode("," $ids) ."\n";

// ----------------------------------------------------------------------------------
// helper function to turn an array of values into an array of value references
// necessary because mysqli_stmt::bind_param needs value refereces for no good reason
function ref($arr) {
    $refs = array();
    foreach ($arr as $key => $val) $refs[$key] = &$arr[$key];
    return $refs;
}

Add more parameter placeholders for other fields as you need them.

Which one to pick?

• The first variant works with a variable number of records iteratively, hitting the database multiple times. This is most useful for UPDATE and INSERT operations.

• The second variant works with a variable number of records too, but it hits the database only once. This is much more efficient than the iterative approach, obviously you can only do the same thing to all affected records. This is most useful for SELECT and DELETE operations, or when you want to UPDATE multiple records with the same data.

Why prepared statements?

• Prepared statements are a lot safer because they make SQL injection attacks impossible. This is the primary reason to use prepared statements, even if it is more work to write them. A sensible habit to get into is: Always use prepared statements, even if you think it's "not really necessary." Neglect will come and bite you (or your customers).
• Re-using the same prepared statement multiple times with different parameter values is more efficient than sending multiple full SQL strings to the database, because the database only needs to compile the statement once and can re-use it as well.
• Only parameter values are sent to the database on execute(), so less data needs to go over the wire when used repeatedly.

In longer loops the execution time difference between using a prepared statement and sending plain SQL will become noticeable.

---

Related Topics