0

I'm looking for a bit of advice on which method is more secure and efficent to use.

OPTION A: I have a database named calendar, and 12 tables in it - "January", "February" etc. Each month has it's own .php page with "select * from jan" etc. I have a dropdown menu, which when the user selects for example "January" I have an ajax script that then loads jan.php into the div "currentmonth"

What I was thinking of doing, because along with the "currentmonth" div, I've also got a "recentlyadded" div showing the last 3 entries sorted by their timestamp. I'm not sure how I could show the 3 most recently added entries in the whole database, so I tried option B.

OPTION B: I have one table called calendar, and each row has a column called month. This made it more simple for display the recently added, but I'm not sure how to go about implementing the dropdown menu. From what I've read the idea I have can leave me open to sql injection.

Here's the idea: I have a jquery variable called "selectedmonth" which is equal to the value of the selected month. I then want to take that variable... for example, user selects "January" the value is "jan", and I want the sql statement to then update with SELECT * FROM calendar WHERE months = "jan", but as I said, I hear this can leave me wide open to SQL injection.

Is there anyway to dynamically update the SQL statement, maybe with AJAX or JSON? Is there a way to do OPTION B without leaving myself open to SQL injection? Or is there an easier way for me to use OPTION A, and be able to find the 3 most recent added rows in the entire database?

The one thing I'm wondering though is with option A, I would obviously have 12 "month".php files, will that add a lot to page load times?

Any advice would be greatly appreciated.

Alan Hill
  • 27
  • 6

1 Answers1

0

Use prepared statements and you should be fine. The second option is preferable, the first is not a good idea...

All you have to do is something like:

$row = $conn->prepare("SELECT * FROM calendar WHERE months = ?");
$row->bind_param('s', $_GET['month']);
$row->execute();
$row = $row->get_result();

And you'll be SQL injection-free. Read more here.

Community
  • 1
  • 1
Shahar
  • 1,687
  • 2
  • 12
  • 18
  • So does this `$sql = "SELECT * FROM events "; $result = $conn->query($sql); if ($result->num_rows > 0) {` echo ..blah blah Just turn into this? `$row = $conn->prepare ("SELECT * FROM events WHERE selectmonth = ? "); $row->bind_param("s", $_GET['selectmonth']); $row->execute(); $row = $conn->get_result(); if ($result->num_rows > 0) {` echo ...blah blah I'm new to sql. When I change the first statement to the second statement it gives me Call to undefined method mysqli::get_result() Or do I need to change the dropdown into a form, with a submit button and method of get? – Alan Hill Jan 20 '15 at 02:07
  • The overall goal was to have it so the user just clicks on the month and the "selectedmonth" div updates with the correct month - no submit button, just dynamically changing. – Alan Hill Jan 20 '15 at 02:08
  • @AlanHill That's because I wrote `$row->get_result();` and not `$conn->get_result();` – Shahar Jan 20 '15 at 02:08
  • Is this correct then: `$row = $conn->prepare ("SELECT * FROM events WHERE selectmonth = ? "); $row->bind_param("s", $_GET['selectmonth']); $row->execute(); $row = $conn->get_result();` Because I keep getting the same error : **Call to undefined method mysqli::get_result()** – Alan Hill Jan 20 '15 at 02:18
  • @AlanHill No, as I said. It shouldn't be `$row = $conn->get_result();`. It should be `$row` instead of `$conn`. `$row = $row->get_result();` – Shahar Jan 20 '15 at 02:19
  • Sorry I misread it. So for `if ($result->num_rows > 0) {` does $result stay the same? Or is $result now $row? – Alan Hill Jan 20 '15 at 02:27
  • It's going to be $row. Check this out: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php Also Google "mysqli prepared statements" for more. – Shahar Jan 20 '15 at 02:29