Can this prepared statement prevent SQL injection?

Question

$string = trim($_POST['string'])
$sql = "INSERT INTO table (string) VALUES(:string)";
$query = $db->prepare($sql);
$query->execute(array(
    ":string" => $string
));

Can this block of code prevent SQL injection?

EDIT:
This is the connection that I am making to the database. Does the charset of this code allows the above block of code to be executed and prevent the SQL injection?

//database credentials
define('DBHOST','localhost');
define('DBUSER','root');
define('DBPASS','');
define('DBNAME','table');

//application address
define('DIR','http://localhost/');
define('SITEEMAIL','noreply@example.com');

try {

//create PDO connection 
$db = new PDO("mysql:host=".DBHOST.";port=3306;dbname=".DBNAME, DBUSER, DBPASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

} catch(PDOException $e) {
//show error
echo 'Looks like server is down please check back later';
exit;
}

You should also make sure the database connection and the database has the same charset encoding. — AnotherGuy, Aug 15 '15 at 11:23
I cannot explain this easily. Luckily there already exists [an awesome answer](http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/12202218#12202218), which explains everything in depth. — AnotherGuy, Aug 15 '15 at 11:31

score 3 · Accepted Answer · answered Aug 15 '15 at 11:14

3

Yes it will prevent SQL injection because

Prepared statements uses bound parameters.

Prepared Statements do not combine variables with SQL strings, so it is not possible for an attacker to modify the SQL statement.

Prepared Statements combine the variable with the compiled SQL statement, this means that the SQL and the variables are sent separately and the variables are just interpreted as strings, not part of the SQL statement.

answered Aug 15 '15 at 11:14

vinoth.kumar

92
12

Thank you! :) I was so worried. – user3436939 Aug 15 '15 at 11:16
If the answer is useful to you can accept it as answer ! so that when new person looking at your question will learn from the answer ! – vinoth.kumar Aug 15 '15 at 11:18
It says I can accept an answer in six minutes! And I haven't done any sanitization of the string, still it'll work, right? – user3436939 Aug 15 '15 at 11:20
@user3436939 - Yes as you are using Prepared statements you don't need this – vinoth.kumar Aug 15 '15 at 11:22
You may still want to sanitize the string, depending on what it is supposed to contain; sanitizing is not the same as escaping – Mark Baker Aug 15 '15 at 11:28
A user told me to update it with the database connection. Please, check it and tell me if it's alright. Sorry, for disturbing again. – user3436939 Aug 15 '15 at 11:29
@MarkBaker, I've completed the whole site with this only. And escaping every string will be time-consuming specially when deadline is near. Now, will it be safe without escaping the string? – user3436939 Aug 15 '15 at 11:31
3

Using prepared statements, you don't need to escape the values.... that's what prepared statements eliminate.... escaping !== sanitizing.... escaping handles quote characters and similar inside the string values that would otherwise break the SQL and cause a risk of SQL injection, using bind vars eliminates this risk.... sanitizing is about eliminating data in the string that you won't necessarily want displaying, such as html markup or similar.... using bind vars does nothing to eliminate this – Mark Baker Aug 15 '15 at 11:38
1

To add to Mark Baker's comment: You typically want to sanitize your HTML on output and keep the original data intact (unaltered) in your database. – Scott Arciszewski Aug 24 '15 at 19:01

Can this prepared statement prevent SQL injection?

1 Answers1