Does using prepared statements in MySQL prevent SQL injection attacks?

Question

I'd just like to verify if using prepared statements in MySQL prevents SQL injection.

Will the following code prevent all SQL injection attacks?

$var = $_GET['q']; 
$trimmed = trim($var);
if ($trimmed != NULL) {
  $get_fighters = $DBH->prepare(
    'SELECT * 
    FROM fighters 
    WHERE name LIKE :searchTerm 
      OR nickname LIKE :searchTerm 
      OR born_in_city LIKE :searchTerm
      OR born_in_state LIKE :searchTerm
      OR born_in_country LIKE :searchTerm
      ORDER BY name ASC');
  $get_fighters->bindValue(':searchTerm', '%' . $trimmed . '%', PDO::PARAM_STR);
  $get_fighters->setFetchMode(PDO::FETCH_ASSOC);
  $get_fighters->execute();
  $check_results_fighters = $get_fighters->rowCount();

  $get_events = $DBH->prepare(
    'SELECT * 
    FROM events 
    WHERE event_name LIKE :searchTerm
      OR event_arena LIKE :searchTerm
      OR event_city LIKE :searchTerm
      OR event_state LIKE :searchTerm
      OR event_country LIKE :searchTerm
      OR organization LIKE :searchTerm
    ORDER BY event_date DESC');

  $get_events->bindValue(':searchTerm', '%' . $trimmed . '%', PDO::PARAM_STR);
  $get_events->setFetchMode(PDO::FETCH_ASSOC);
  $get_events->execute();
  $check_results_events = $get_events->rowCount(); 
}

possible duplicate of [Best way to prevent SQL Injection in PHP](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) — outis, Jun 16 '12 at 04:46
From the [`PDO::prepare`](http://php.net/PDO.prepare) docs: "You cannot use a named parameter marker of the same name twice in a prepared statement." (Though emulated prepared statements in some versions of PHP with the PDO/MySQL driver do support repeated names, it's not safe to rely on this; see also ["php pdo prepare repetitive variables"](http://stackoverflow.com/q/7603896/).) Always RTM. — outis, Jun 16 '12 at 04:49
eeek.. Does that mean I literally have to do searchTerm1, 2, 3, 4, etc... for each individual parameter despite that they're all the same value? — zen, Jun 16 '12 at 04:54
Outside of asking for clarification, comments shouldn't be used to ask additional questions. For one thing, a question should be understandable without reading comments. For another, SO is a Q&A site, not a forum, and comments aren't intended (nor are they well suited) for discussions. When you have additional requirements that aren't yet described in the question, edit the question rather than posting a comment. If you have questions beyond what you originally asked (as here), first check whether the question has already been asked (which it has), and post a new question only if not. — outis, Jun 16 '12 at 04:57

score 7 · Accepted Answer · answered Jun 16 '12 at 04:21

Prepared queries prevent attacks by separating the query to be ran, and the data to be used for that query. That means that a first-order attack cannot occur, since you're not concatenating data directly into the query.

In short, if you always use prepared queries, and all of your data is sent with bound parameters (including data from other queries!) then you are fine, as far as SQL injection goes.

(I should also note that some PDO drivers for servers that do not support prepared queries will fake it with traditional escape routines. Don't concern yourself with this. It's safe.)

score 3 · Answer 2 · answered Jun 16 '12 at 04:19

3

Yes, using prepared statements will prevent SQL Injection according to the php documentation.

See Link

answered Jun 16 '12 at 04:19

secretformula

6,414
3
33
56

Does using prepared statements in MySQL prevent SQL injection attacks?

2 Answers2