Use session name in MySQL statement

Question

I'm having problem with my php code. I need to query db using in WHERE session name from php page. Below code:

            <?php session_start();
                try { 
                  //$con= new PDO('mysql:host=localhost;dbname=wydania_tonery', "sa", "sql");
                  $zmien_kodowanie = $dbo->query("SET names 'utf8'");
                  $sesja = $_SESSION['username'];
                  $query = "SELECT
                                mdl_user.firstname AS 'Imie',
                                mdl_user.lastname AS 'Nazwisko',
                                mdl_user.department AS 'Akronim',
                                reg.data AS 'Region',
                                sta.data AS 'Stanowisko',
                                mdl_user.aim AS 'Stan',
                                NULLIF('Uwagi', 0) AS 'Uwagi'
                            FROM
                                mdl_user 
                            LEFT JOIN 
                                mdl_user_info_data AS reg ON reg.userid=mdl_user.id AND reg.fieldid='2' 
                            LEFT JOIN 
                                mdl_user_info_data AS sta ON sta.userid=mdl_user.id AND sta.fieldid='4' 
                            WHERE 
                                mdl_user.email LIKE 'kierownik.%' AND mdl_user.deleted='0' AND mdl_user_info_data.fieldid = ".$_SESSION['username']." /*AND mdl_user.department='BBT'*/ 
                            ORDER BY 
                                mdl_user.department ASC";

                  //first pass just gets the column name
                  echo "<table> \n";
                  $result = $dbo->query($query);
                  //return only the first row (we only need field names)
                  $row = $result->fetch(PDO::FETCH_ASSOC);
                  print " <tr> \n";
                  foreach ($row as $field => $value){
                   print " <th>$field</th> \n";
                  } // end foreach
                  print " </tr> \n";
                  //second query gets the data
                  $data = $dbo->query($query);
                  $data->setFetchMode(PDO::FETCH_ASSOC);
                  foreach($data as $row){
                   print " <tr> \n";
                   foreach ($row as $name=>$value){
                   print " <td>$value</td> \n";
                   } // end field loop
                   print " </tr> \n";
                  } // end record loop
                  print "</table> \n";
                  } catch(PDOException $e) {
                   echo 'ERROR: ' . $e->getMessage();
                  } // end try
            ?>

When I use standard way (".$variable.") to pass php variable I got error below:

Fatal error: Call to a member function fetch() on boolean in Path/to/my/page on line 35

If anyone would like to help me solve my problem I'd be glad to thank him

Your problem is that `fetch()` is not defined on a boolean, which results from you assuming that `$result` is not a boolean, which it would be if there was an error. The real question is what is the real error and not the PHP code consequence of that error? — apokryfos, Aug 17 '16 at 11:52

score 1 · Answer 1 · answered Aug 17 '16 at 11:55

Try

        <?php session_start();
            try { 
              //$con= new PDO('mysql:host=localhost;dbname=wydania_tonery', "sa", "sql");
              $zmien_kodowanie = $dbo->query("SET names 'utf8'");
              $sesja = $_SESSION['username'];
              $query = "SELECT
                            mdl_user.firstname AS 'Imie',
                            mdl_user.lastname AS 'Nazwisko',
                            mdl_user.department AS 'Akronim',
                            reg.data AS 'Region',
                            sta.data AS 'Stanowisko',
                            mdl_user.aim AS 'Stan',
                            NULLIF('Uwagi', 0) AS 'Uwagi'
                        FROM
                            mdl_user 
                        LEFT JOIN 
                            mdl_user_info_data AS reg ON reg.userid=mdl_user.id AND reg.fieldid='2' 
                        LEFT JOIN 
                            mdl_user_info_data AS sta ON sta.userid=mdl_user.id AND sta.fieldid='4' 
                        WHERE 
                            mdl_user.email LIKE 'kierownik.%' AND mdl_user.deleted='0' AND mdl_user_info_data.fieldid = ? /*AND mdl_user.department='BBT'*/ 
                        ORDER BY 
                            mdl_user.department ASC";

              //first pass just gets the column name
              echo "<table> \n";
              $stmt = $dbo->prepare($query);
              $stmt->bind_param("s", $_SESSION['username']);                  
              $result = $stmt->execute();

No luck ... ;( page say that bind_param is undifined method in PDO — bgolemba, Aug 17 '16 at 12:01
Use `$stmt->bindParam(1,$_SESSION['username'],PDO::PARAM_STR)` — apokryfos, Aug 17 '16 at 12:37
Unfortunately doesn't work. Throws error: Fatal error: Call to a member function bindParam() on null in Path/to/my/page on line 34 — bgolemba, Aug 18 '16 at 05:25
Yes if I echo $_SESSION['username'] it display me value which I want to use in query. Maybe this will help but this code which I sent is a part of other page, on main page I include this code to display result of query in . $_SESSION['username'] comes from main page — bgolemba, Aug 18 '16 at 06:00

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

Instead of using direct substitution values, you could use below methods to avoid sql injection.

You basically have two options to achieve this:

Using PDO:

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Please refer How can I prevent SQL-injection in PHP?

edited May 23 '17 at 12:15

Community

1
1

answered Aug 17 '16 at 15:01

Tamil

1,193
9
24

Use session name in MySQL statement

2 Answers2