2

I have finally figured out how to use array elements as a part of a WHERE clause, but now I am not sure if the following query is protected against SQL injection.

$elements = ( implode ( "', '", $array ) );
if ( $stmt = $mysqli -> prepare ( "SELECT * FROM config_errors WHERE error_assoc_id IN ('$elements') AND row_type = ?" ) ) {
  $row_type = "some_value";
  $stmt -> bind_param ( "s", $row_type );
  ...
}

To be honest, I am very deficient in this regard. Thanks for any advice.

EDIT (@Wing Lian) It's not the important thing but $array is created in another statement:

$array = array();
if ( $stmt = $mysqli -> prepare ( "SELECT * FROM config_errors WHERE row_type = ? AND error_type = ?" ) ) {
  bind params
  execution of the query and the result
  mysqli_num_rows condition
  while $row loop
  value (characters) conditions
  array_push ( $array, $row['value'] );
}

Let's assume that the array is created from inputs of some form.

falcon
  • 357
  • 2
  • 5
  • 21
  • Prepare statement and bind_param will protect you from SQL injection. So you are good :) – TheLittleHawk Dec 01 '14 at 01:27
  • No it isn't. You can either reinvent the wheel, or use any decent wrapper class that implements array binding. The quickest workaround would be `array_map([$pdo, "quote"], $array)`. But you're using `mysqli`. Condolences. – mario Dec 01 '14 at 01:27
  • @TheLittleHawk Hi, thanks for your reply! Are you sure I am good? Because `bind_param` function is not applied to `$elements` variable... – falcon Dec 01 '14 at 01:36
  • @mario Thanks for condolences. – falcon Dec 01 '14 at 01:37
  • Read up on "2nd order injection". The superficial "prepared statements are enough" answer isn't anywhere near factual. – mario Dec 01 '14 at 02:21
  • @mario What should I do? Have I learn to work with PDO? Why nobody told, when the `mysqli` extension has started and `mysql_` has become to be obsolete, "dont learn mysqli", "it can not be protected" and so on? It is not so far from today. Why is mysqli still "alive" if it is hazardous? What is factual? Thank you – falcon Dec 01 '14 at 02:30
  • There are a few pitfalls with `mysqli` and it's bad advise to newcomers. (Many incorrectly presume it was a simpler switch because you just had to append an `i` everywhere). You can write as perfectly secure code with mysqli as with PDO. It just happens to be more effort - and long-windedness seldomly benefits security. It's not going to get depreciated, but it's just a stop-gap API; hardly anyone uses it for new projects. – mario Dec 01 '14 at 02:50
  • @mario Marked as duplicate? Mario please tell me, what this link to which you refer has to do with my contribution? The main subject of this topic is SQL injection. – falcon Dec 01 '14 at 03:00
  • @falcon And that's covered perfectly well by the referenced question. (Please read it). A duplicate-close does not imply that your question is bad, btw. Au contraire, you actually invested some effort here, and obviously want to learn something. You however accepted the hogwash answer, which could likely send future visitors on the wrong path. So instead of answering this topic for the 250th time this week, I thought it more useful to cross-reference something correct. – mario Dec 01 '14 at 03:07
  • @mario I have nothing to say here anymore – falcon Dec 01 '14 at 03:12

2 Answers2

1

Since you are using prepare statement, your application will be protected from SQL Injection.

As of PHP documention:

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Rest of documentation

TheLittleHawk
  • 628
  • 3
  • 22
  • So there is no need to quote parameters of the statement. It looks you are right. Thank you very much, also for the link! ☺ – falcon Dec 01 '14 at 01:48
  • The `IN` values in OPs example aren't covered by the prepared statements. They're not bound as parameters, but just **interpolated**. Even if those values originate from a previous database query they can contain SQL string enclosures, so that data may bleed into code context for downstream operations. – mario Dec 01 '14 at 02:55
0

Unfortunately you are not protected against SQL injection if anything in $array comes from any form of request input that you use in the WHERE IN () statement. See Can I bind an array to an IN() condition? on how to properly bind parameters for the IN condition.

Community
  • 1
  • 1
Wing Lian
  • 2,387
  • 16
  • 14