0

I would like to know is it ok to use variables in mysql functions inside php? While ofcourse making sure that the variables values not being received directly from client inputs.

For example is it better to use one of those queries on the other?

$stat = $db->prepare("INSERT INTO table(`col1`, `col2`, `image`) 
VALUES ('$val1', '$val2')");

$stmt = $dbh->prepare("INSERT INTO table(`col1`, `col2`) 
VALUES (:val1, :val2)");

$stmt->bindParam('col1',$val1);
$stmt->bindParam('col2',$val2);
ADyson
  • 57,178
  • 14
  • 51
  • 63
Alsakka
  • 155
  • 10
  • 2
    Always always always use parameters. See the SQL injection article in the bue box above. Even if input isn't coming directly from a user, it's still potentially a risk (unless perhaps you literally hard-coded the variable values into your PHP application code). And as well as injection problems, parameterisation also protects you from potential silly unexpected syntax errors due to the content of the input. e.g. a simple `'` in one of the values would break the SQL formatting in your first example. And parameters are easy to use, so there's simply no reason not to use them. – ADyson Jun 10 '22 at 13:15
  • 1
    *Never* use variables like in the first example. – Alex Howansky Jun 10 '22 at 13:16
  • 1
    How do you know that a variable is "not being received directly from client inputs"? How do you know that a variable does not contain some special character? – Your Common Sense Jun 10 '22 at 13:18
  • 1
    If you're trying to avoid extra code you can pass all bindings in the `execute` function. `$stmt = $dbh->prepare("INSERT INTO table(\`col1\`, \`col2\`) VALUES (:val1, :val2)"); $stmt->execute(array(':val1' => $val1, ':val2' => $val2));` – user3783243 Jun 10 '22 at 13:19
  • @YourCommonSense That I would for example sanitize the input and re check it's values etc before placing it in the query – Alsakka Jun 10 '22 at 13:22
  • @user3783243 would that still apply more indirect inserting like bindParam? – Alsakka Jun 10 '22 at 13:23
  • `I would for example sanitize the input and re check it's values etc before placing it in the query`...but the only way to fully "sanitise" against SQL injection is to use prepared statements and parameters. It's not clear precisely what you're proposing with that description, but basically no other known methods are sufficient, they will all have flaws (or worse, be completely irrelevant to the problem, which is what we commonly see when people suggest such things here. There is a lot of really bad advice out there on blogs and forums, sadly). – ADyson Jun 10 '22 at 13:25
  • @Alsakka By `indirect inserting` do you mean binding? Yes, that would be the same as binding but less lines because you dont need a `bind*(` call for each value/param. You should not sanitize. – user3783243 Jun 10 '22 at 13:26
  • 1. What do you mean "sanitize"? Are you sure your "sanitization" is 100% safe? 2. Why? – Your Common Sense Jun 10 '22 at 13:26
  • @Alsakka `would that apply more indirect inserting`...yes. You can [read the manual](https://www.php.net/manual/en/pdostatement.execute.php) to know this. – ADyson Jun 10 '22 at 13:26
  • Yes, like you guys guessed it I followed some steps to make sure "in this case I have now which is to insert name, number into table" to make sure the variable only contains letters for name and number for **number** with **regex** for example plus removing any slashes etc on input. And Thanks for clarifying that this works as binding! – Alsakka Jun 10 '22 at 13:32
  • 1
    Yeah...that's simply input format validation according to your business rules, not SQL injection santisation. We could not be sure it would prevent all forms of SQL attack. Just use parameters and then you don't need to worry about it, the problem is already solved for you :-) – ADyson Jun 10 '22 at 13:34

0 Answers0