rechecking my PDO

[Richard_Grant]

Okay i have a function

function p_d_o($options, $func){//$options is a multidimensional array

..Parse $option to strings..
//when echoed $query looks like this:

//$vars[":val_0", "14084"]; // this is the only value retrieved from $_POST or $_GET all other values are from PHP function calls
//SELECT RAND_CODE,USERNAME FROM table WHERE RAND_CODE LIKE :val_0 LIMIT 5";
     $query = $cols_ . ' FROM ' . $options["table"] . ' ' . $options["conditional"] . ' ' . (($options["limitted"])?  $cond_ . ' LIMIT ' . $options["limitted"] : "");
          $secure_mysqli = $con->prepare($query);

          $secure_mysqli->execute($vars); //vars contains :val_0
     while($item = $secure_mysqli->fetch(PDO::FETCH_ASSOC)){
          $func($query);
     }
}

My question is Should i be worried about the other parts of the query string that is not being binded? I was having issues binding table name and cols.

[Jacques1]

You indeed cannot pass identifiers like column or table names to a prepared statements, and that's indeed a problem.

 

There are basically two solutions: Either you avoid this scenario altogether, or you use a whitelist of acceptable identifiers. Theoretically, you might also try a kind of generic validation so that you can use entirely dynamic queries (which seems to be your goal). But this is risky and fragile, so I'd avoid it at all cost. Even the big database frameworks shy away from this.

[Richard_Grant]

  On 9/11/2014 at 3:29 AM, Jacques1 said:

You indeed cannot pass identifiers like column or table names to a prepared statements, and that's indeed a problem.

 

There are basically two solutions: Either you avoid this scenario altogether, or you use a whitelist of acceptable identifiers. Theoretically, you might also try a kind of generic validation so that you can use entirely dynamic queries (which seems to be your goal). But this is risky and fragile, so I'd avoid it at all cost. Even the big database frameworks shy away from this.

 

Would you scold me for using mysqli_real_escape_string ? (if it would even let me use my pdo connection as the connection variable)

[Jacques1]

  On 9/11/2014 at 4:01 AM, Richard_Grant said:

Would you scold me for using mysqli_real_escape_string ?

 

You mean for identifiers? This doesn't work. mysqli_real_escape_string() only works within quoted string values. It only prevents users from “breaking out” of the quoted expression. If there are no quotes, the whole function is useless.

 

By the way, the PDO equivalent of mysqli_real_escape_string() is PDO::quote().

[Richard_Grant]

  On 9/11/2014 at 4:18 AM, Jacques1 said:

You mean for identifiers? This doesn't work. mysqli_real_escape_string() only works within quoted string values. It only prevents users from “breaking out” of the quoted expression. If there are no quotes, the whole function is useless.

 

By the way, the PDO equivalent of mysqli_real_escape_string() is PDO::quote().

 

My table name and col are passed in as a string parameter so PDO::quote should work just fine for me

[Jacques1]

I think this is a misunderstanding. If you put the table name into an SQL string, you'll get a syntax error:

SELECT * FROM 'this_is_invalid';

Identifiers are not strings, so you cannot use PDO::quote().

 

Like I said, there's no easy solution. In fact, it's impossible to prevent SQL injections with the function itself, because you want it to accept complete SQL clauses (like the WHERE clause). Your only chance is to carefully validate all input before you pass it to the function.

 

You should also consider using professional database frameworks like Doctrine. They don't entirely solve the problem either, but they're more mature and easier to control.

[Richard_Grant]

  On 9/11/2014 at 4:46 AM, Jacques1 said:

I think this is a misunderstanding. If you put the table name into an SQL string, you'll get a syntax error:

SELECT * FROM 'this_is_invalid';

Identifiers are not strings, so you cannot use PDO::quote().

 

Like I said, there's no easy solution. In fact, it's impossible to prevent SQL injections with the function itself, because you want it to accept complete SQL clauses (like the WHERE clause). Your only chance is to carefully validate all input before you pass it to the function.

 

You should also consider using professional database frameworks like Doctrine. They don't entirely solve the problem either, but they're more mature and easier to control.

 

. Its really spaghetti and if i didn't spend hours setting up my server to use PDO i would switch to mysqli

[Jacques1]

This has nothing to do with PDO, it's the same thing with MySQLi. In fact, you'd have to write twice as much code for MySQLi.

[Richard_Grant]

  On 9/11/2014 at 5:44 AM, Jacques1 said:

This has nothing to do with PDO, it's the same thing with MySQLi. In fact, you'd have to write twice as much code for MySQLi.

I was talking about not using prepared statements my bad about that.