-2

I am using the method below to get values from the database (from the "$column" column) and it is working as intended but i would like to know the correct way to implement the "$column" variable that is added in the SELECT statement, so as to be as safe as possible from injection (either by preparing with a ?-type placeholder or by properly escaping). What would be the most modern and safe approach? NOTE: $qry->bind_param("ss",$column,$rowName); with 2 ? placeholders doesn't work.

$column = $_POST['column'];
$rowName = $_POST['rowName'];

$qry = $connection->prepare("SELECT $column FROM database_name WHERE row_name=?");
$qry->bind_param("s",$rowName);
$qry->execute();
$result = $qry->get_result();
greencookie
  • 5
  • 3
  • 2
    You cannot prepare table and column names, but you can use variables that you check against a white-list of columns. – Jay Blanchard Mar 23 '17 at 20:47
  • why was this question reopened? and who reopened it? – Funk Forty Niner Mar 23 '17 at 21:07
  • for the "who", I just found out who: [YCS](http://stackoverflow.com/users/285587/your-common-sense) => http://stackoverflow.com/posts/42986569/revisions - he had no grounds to do that, based on what the OP posted *"NOTE: $qry->bind_param("ss",$column,$rowName); with 2 ? placeholders doesn't work."* – Funk Forty Niner Mar 23 '17 at 21:16
  • @dana I honestly don't know why you deleted your "correct" answer. I hope you didn't feel "pressured" in your decision. – Funk Forty Niner Mar 23 '17 at 21:20
  • I apologize if there was a similar question that i couldn't find, i was actually seeking for a "best practices" type of answer. Thanks for all the replies. Is there a way to "find" that previous answer that was deleted? – greencookie Mar 24 '17 at 05:09

1 Answers1

0
$column = $_POST['column'];
$rowName = $_POST['rowName'];

$qry = $connection->prepare("SELECT * FROM database_name WHERE row_name=?");
$qry->bind_param("s",$rowName);
$qry->execute();
$result = $qry->get_result();
$value = $result[$column];
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • That is indeed a perfectly acceptable solution, but if i had many columns and many such searches, wouldn't it be less costly to only choose the column i need in the first place? – greencookie Mar 23 '17 at 20:48
  • While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Jay Blanchard Mar 23 '17 at 20:48
  • @greencookie *when* you will have many such searches, then it will be time to optimize. In reality you won't. – Your Common Sense Mar 23 '17 at 20:54
  • You have got some nerve [reopening](http://stackoverflow.com/posts/42986569/revisions) that question. Their *"NOTE: $qry->bind_param("ss",$column,$rowName); with 2 ? placeholders doesn't work."* is just that; can't bind tables/columns. – Funk Forty Niner Mar 23 '17 at 21:13
  • @YourCommonSense since i am definitely quite new to this concept i would be very interested on some general guidelines as to what it means to "optimize". Should i change the way my database is set up? It's more of a general "best practices" answer i am seeking actually. Still thanks for the replies and i apologize if there was a similar question that i couldn't find – greencookie Mar 24 '17 at 05:07
  • @greencookie the only guideline for the moment is a golden rule that says: "Premature optimization is the root of all evil." Means you shouldn't care whether some thing will be "costly" or not without any apparent evidence – Your Common Sense Mar 24 '17 at 05:12
  • @greencookie in general, you never select just one field from the record. instead, you select the whole record once and then take from it whatever field you need. That's how it's done by everyone. I wonder what made you think you need such a function at all – Your Common Sense Mar 24 '17 at 13:01
  • Probably the fact that i am a newbie :p – greencookie Mar 25 '17 at 08:24