-1

Many references like W3schools and Wikipedia state "If the original statement template is not derived from external input, SQL injection cannot occur".

What does this mean exactly? I've tried to search more on the subject but most references leave it at exactly that.

To be more specific, I am talking about: If the original statement template is not derived from external input

As, I've researched as to why it cannot occur.

Does this mean, as long as you don't directly put the user input into the query, like:

SELECT * FROM pubs WHERE " . $_POST['extra'] . " LIKE '%Yes%'

as opposed to: 

SELECT * FROM pubs WHERE ? LIKE ?

Also, is it okay to pass a column name as a parameter like i've done above. For instance say I have 

$extra = $_POST['extra'];
$yes = "%Yes%";

and then I prepare: 

$stmt = $mysqli->prepare(SELECT * FROM pubs WHERE ? LIKE ?);

and then bind:

$stmt->bind_param("ss", $extra, $yes);

Is this safe an correct?

coopwatts
  • 670
  • 1
  • 8
  • 31
  • 1
    *"If the original statement template is not derived from external input"* - meaning that if your querying based on user input and you haven't escaped that data, then you'd be open to an SQL injection (external input also means "not in your control"). 2nd part: yes it is safe. Make sure though that your form is safe from XSS injection. – Funk Forty Niner Nov 11 '15 at 19:44
  • This however `SELECT * FROM pubs WHERE ? LIKE ?` is incorrect. It should read as `SELECT * FROM pubs WHERE col=? AND|OR LIKE ?` – Funk Forty Niner Nov 11 '15 at 19:50
  • @Fred-ii- considering the question was about the column names specifically and not the value (which isn't covered in the accepted answer on that question) I don't really see this as a duplicate of that. – Ali Hamze Nov 11 '15 at 19:57
  • @AliHamze The question is mainly about "user input". They obviously have a syntax error, however all of the proper syntax is in the duplicate question, which I feel is a duplicate to the "master question" itself. – Funk Forty Niner Nov 11 '15 at 20:02
  • @Fred-ii- While yes they have a syntax error, the code you provided (as well as the one in the question linked) assume that they know the column names. So something like `SELECT * FROM pubs WHERE col=? AND|OR LIKE ?` would not work in this case. If you look at my answer, you will see that the column name is an unknown in this case and is user-submitted. – Ali Hamze Nov 11 '15 at 20:04
  • @AliHamze that was a mere comment/example. I don't know where you're going with this, or where you want to take this too. `SELECT * FROM pubs WHERE col=? AND|OR LIKE ?` - `AND|OR` is their choice of `AND` or `OR`, if that's what you're talking about. – Funk Forty Niner Nov 11 '15 at 20:05
  • @Fred-ii- I know that that code itself obviously won't work with the `AND|OR` in there and that you meant it as their choice. However, the part I'm having an issue with is that you assume that they know the column name in advance. In your example, the column name is `col`. The question states that they do not know the column name as it is user-submitted. I feel this question shouldn't be closed as a duplicated (at least not one of the question linked since it doesn't cover cases where the column name is unknown) – Ali Hamze Nov 11 '15 at 20:09

1 Answers1

0

You should not use user input in a query before checking it. Also, column names can not be parameterized so your example won't work. What you should do is have a list of allowed column names and only perform the query if the user input is in the accept list.

$allowedColumnNames = ['city', 'state', 'zip'];
if (in_array($_POST['extra'], $allowedColumnNames)) {
    //Perform your query.
    //SELECT * FROM pubs WHERE $_POST['extra'] LIKE ?
}
Ali Hamze
  • 1,590
  • 13
  • 26
  • This solves a big problem of mine. Thank you – coopwatts Nov 11 '15 at 19:51
  • Is this still safe from injection? – coopwatts Nov 11 '15 at 20:05
  • @cwattsdis Yes as you are checking the input before use and only allowing specific values. As for the value of the `?`, it doesn't matter because the prepared statement ensures that no sql code in that value is executed and is treated as a value instead. If this answer helped you, please press the check mark to the left to accept it. – Ali Hamze Nov 11 '15 at 20:06
  • Yes I am going to accept it, my apologies, there was a time restriction on the accepting answer. Thank you for the clarification – coopwatts Nov 11 '15 at 20:14