可以使用表达式引擎中的'username'全局变量从mysql数据库中选择记录吗？

Question

• I am trying to create a form for the parents in a school to book after-school care for their children through the school website, which is built using Expression Engine.
• Parents would log-in with their their family ID (a number matching the family record number in the database). Then, on the booking page, they would select from a drop-down list of their own children which child they would like to make the booking for.

On the landing page after parent log-in, I have put the following PHP code:

 <?php
    $_SESSION['familyid']='{username}';
    $_SESSION['family']='{screen_name}';
    ?>

I did this on a separate page to the booking page because I thought there might be an issue with global variables being parsed last in ExpressionEngine. Then on the booking page, I have:

$con=mysqli_connect("server","database","password");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    $sql = "SELECT ChildID, Child FROM Children WHERE FamilyID =       '".$_SESSION['familyid']."'";
    $result = $con->query($sql);
    $data = mysql_fetch_array($result);

but this does not return any children. If I give the session variable a valid number manually, it works, but I can't get the sql to work where the username has been passed to the variable. Is what I'm trying to do even possible?

Answer 1

Severe security issues with this - Ideally you want to keep it all in EE and don't enable PHP.

The correct way would be to have a module or plugin with the database credentials stored in the database.php config file, you can read a guide here: http://www.99bugs.com/adding-external-databases-to-expression-engine-project/

Or there's an add-on that will do this for you:

<ul>
  {exp:external_entries:select 
    hostname="mysql.deathstar.com" 
    username="deathstar_vader" 
    password="luk35dad" 
    database="deathstar_plans"  
    table="Children" 
    search:FamilyID="{username}"
    orderby="Child" 
    sort="asc"} 
    <li>
      Child ID = {ChildID}
      Child = {Child}
    </li>
  {/exp:external_entries:select}
</ul>

The examples from the plugin still have the insecure DB credentials in the template - but the comments mention that these can be stored in the config, but no details as to format, so a quick look into the plugin should shed some light on this.

Ideally you'd have the Children table in the EE database and do something like this native to EE using this free add-on (EE2 only - not EE3) to save your session data on the landing page after login:

{exp:session_variables:set name="familyid" value="{username}"}
{exp:session_variables:set name="family" value="{screen_name}"}

Then on the booking page use the query tag:

<ul>
  {exp:query sql="SELECT ChildID, Child FROM Children WHERE FamilyID = '{exp:session_variables:get name="familyid"}'"}
    <li>
      Child ID = {ChildID}
      Child = {Child}
    </li>
  {/exp:query}
</ul>

But because the variables are actually always available, you don't need to save them in a session, as long as the user is still logged in:

<ul>
  {exp:query sql="SELECT ChildID, Child FROM Children WHERE FamilyID = '{username}'"}
    <li>
      Child ID = {ChildID}
      Child = {Child}
    </li>
  {/exp:query}
</ul>

Edit - Just downloaded and looked at the code for External Entries: there is no config settings, which is odd as it's simple to do, so could do with tweaking to make it even more secure.