Can't get variable working in query on php file

Question

<?php
      echo "
          <html>
            <body style=\"background-color:#d3ddd1\">
              <form method=\"post\" name=\"report\" >
              <p>Counselor Report</p>
              <p>Enter email address</p>
              <input type=\"email\" name=\"email\" /><br />
              <p>Select a start date</p>
              <input type=\"date\" name=\"from\" /><br />
              <p>Select an end date</p>
              <input type=\"date\" name=\"until\" /><br />
              <p>Click Below</p>
              <input type=\"submit\" value=\"run report\" />

              </form>
            </body>
           </html> ";
  function get_report() 
     {
      $e_mail = $_POST['email'];
      include ('dbconn.php');
      $sql = "SELECT a.user_email,a.ID, b.ID, b.post_title \n"
       . " FROM\n"
       . " wp_posts b\n"
       . " INNER JOIN\n"
       . " wp_users a\n"
       . " ON\n"
       . " a.user_email ='".$e_mail."' AND a.ID=b.ID\n"
       . " ORDER BY\n"
       . " post_date";
       $result = $conn->query($sql);
         var_dump($results);
 }
  get_report();
  ?>'

First time question. I can use a real email address in the query with phpmyadmin and get a proper return, I try to incorporate a variable in the php query to the db and get a return of NULL, which is not the same return. No errors reported.

Question: What syntax do I use with the variable to enable functionality with php.?

I'm learning fabio, would you explain a bit about the vulnerability/ — Ray Dunnigun, Mar 16 '16 at 00:49
Check here http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Fabio, Mar 16 '16 at 00:50
Thank you kindly, I have bookmarked that and will utilize this info. I haven't even studied about prepared statements yet. — Ray Dunnigun, Mar 16 '16 at 00:57

Darwin von Corax · Accepted Answer · 2016-03-16T01:08:20.717

2

$result = $conn->query($sql);
var_dump($results);

Check the spelling of your variable name.

Having said that, you really should be using a prepared statement for this. The syntax would be

$sql = "SELECT a.user_email, a.ID, b.ID, b.post_title
         FROM wp_posts b
         INNER JOIN wp_users a
           ON a.ID = b.ID
         WHERE a.user_email = ?
         ORDER BY post_date";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $e_mail);  // 's' means param is a string
$stmt->execute();
$result = $stmt->get_result(); // returns a mysqli_result object

The clause WHERE a.user_email = ? includes a parameter placeholder which will be filled in later by a call to mysqli_stmt::bind_param().

In addition to helping protect against SQL injection, prepared statements automagically handle parameter type matching, quoting and escaping for you.

As is my custom, I leave error handling as an exercise for the reader.

edited Mar 16 '16 at 01:08

answered Mar 16 '16 at 00:43

Darwin von Corax

5,201
3
17
28

just began learning php last week, I'm sure my efficiency will improve over time. I wanted to get the basic query functioning and then scale the code. – Ray Dunnigun Mar 16 '16 at 00:53
Sure. I'll throw some prepared statement code into my answer. – Darwin von Corax Mar 16 '16 at 00:55
I installed php5-mysqlnd which made all that work, and return the same results I was getting , so thank you. I still don't completely comprehend the method, but I know where to go to learn. Thanks again. – Ray Dunnigun Mar 16 '16 at 14:42
[link] pastebin.com/0zeHVjU3 [/link] I failed using the statements on this but reverted to string escape. If you have a minute to spare. I'd value an opinion. Else I maintain appreciation for your help – Ray Dunnigun Mar 18 '16 at 19:33
Don't feel bad; you actually can't do DDL with prepared statements. You should still avoid the deprecated `mysql` API; you can do the same as you did using `mysqli::real_escape_string()` or `PDO::quote()`. – Darwin von Corax Mar 18 '16 at 20:03
Gracias Sir, Now, MY database is actually, MariaDB I suspected I didn't have something plugged in right, because the mysqli functions were failing, so I went to mysql instead. I'll check my extensions pronto! – Ray Dunnigun Mar 18 '16 at 22:02

Can't get variable working in query on php file

1 Answers1