为什么我的while循环看起来总是假的

up9lanfz  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(312)

我一直在尝试让我的php代码显示mysql数据库中的条目。在某种意义上,我不需要手动打印表格,而是根据alogrythm打印表格,我想让它自动运行,但它不能像预期的那样工作。
我尝试过不同的设置表格的方法,但都不管用,我得到的最多的是打印表格中的一个条目,然后在后面吐出错误。

$base = $_POST["base"];
$connection = mysqli_connect("localhost","login","pass") or die("Impossible to connect to the database!");
$db = mysqli_select_db($connection, "database")or die("Impossible to download the database!");
$sql = "SELECT * FROM $base";
$mysqli_result = mysqli_query($connection, $sql);
$sql2 = "SHOW COLUMNS FROM $base";
$set1 = mysqli_query($connection, $sql2);
$colu = array();
while($db = mysqli_fetch_row($set1)){
  $colu[] = $db[0]; }
$columns=implode("<br/>",$colu);
echo "<TABLE BORDER=1>";
echo "<TR><TH>$colu[0]</TH><TH>$colu[1]</TH><TH>$colu[2]</TH><TH>$colu[3]</TH><TH>$colu[4]</TH><TH>$colu[5]</TH></TR>";
while ($row = mysqli_fetch_array($set1)) {
$colu[0] = $row["echo $colu[0]"];
$colu[1] = $row["echo $colu[1]"];
$colu[2] = $row["echo $colu[2]"];
$colu[3] = $row["echo $colu[3]"];
$colu[4] = $row["echo $colu[4]"];
$colu[5] = $row["echo $colu[5]"];
echo "<TR><TD>$colu[0]</TD><TD>$colu[1]</TD><TD>$colu[2]</TD><TD>$colu[3]</TD><TD>$colu[4]</TD><TD>$colu[5]</TD></TR>";}

echo "</TABLE>";
mysqli_free_result($mysqli_result);
mysqli_close($connection); ?>

$_post[$base];部分工作,我猜问题是在while循环,因为它没有完成一次,我不知道为什么它不想工作。

pgx2nnw8

pgx2nnw81#

我看到你的剧本有些问题。如果要获取列作为标题和表体内容,则使用了错误的结果集。

// next line make it possible to do sql insertion, and what if $base has no input?
$base = $_POST["base"];
// the die will never be reached
$connection = mysqli_connect("localhost","login","pass") or die("Impossible to connect to the database!");
$db = mysqli_select_db($connection, "database")or die("Impossible to download the database!");
// where is this query for?
$sql = "SELECT * FROM $base";
// where is this result being used
$mysqli_result = mysqli_query($connection, $sql);

$sql2 = "SHOW COLUMNS FROM $base";
$set1 = mysqli_query($connection, $sql2);
$colu = [];
// what if the table order chages? Best to use mysqli_fetch_assoc
while($db = mysqli_fetch_row($set1)){
    $colu[] = $db[0]; 
}
// where do you use $comumns?
$columns = implode("<br/>", $colu);

echo "<TABLE BORDER=1>";
echo "<TR><TH>$colu[0]</TH><TH>$colu[1]</TH><TH>$colu[2]</TH><TH>$colu[3]</TH><TH>$colu[4]</TH><TH>$colu[5]</TH></TR>";
// you already fetched all record from set1
while ($row = mysqli_fetch_array($set1)) {
    $colu[0] = $row["echo $colu[0]"];
    $colu[1] = $row["echo $colu[1]"];
    $colu[2] = $row["echo $colu[2]"];
    $colu[3] = $row["echo $colu[3]"];
    $colu[4] = $row["echo $colu[4]"];
    $colu[5] = $row["echo $colu[5]"];
    echo "<TR><TD>$colu[0]</TD><TD>$colu[1]</TD><TD>$colu[2]</TD><TD>$colu[3]</TD><TD>$colu[4]</TD><TD>$colu[5]</TD></TR>";
}

echo "</TABLE>";
mysqli_free_result($mysqli_result);
mysqli_close($connection); ?>
o2gm4chl

o2gm4chl2#

你现在面临的最大问题是:

$base = $_POST["base"]
$sql = "SELECT * FROM $base";
$sql2 = "SHOW COLUMNS FROM $base";

这是一个巨大的sql注入漏洞,即使他们只传递一个空字符串给它,这都是不好的。例如,这将导致查询错误,并且根据服务器上的设置和错误报告,您可能会公开相当多的信息。一个例子是堆栈跟踪可能包含db密码等。
与其直接使用用户输入,不如创建如下白名单:

$tables = ['user', 'user_meta', 'states']; //etc

$base =  !empty($_POST["base"]) && false !== ($index = array_search($_POST["base"], $tables)) ? $tables[$index] : false;

if(!$base) die('Unknown table '.$_POST["base"]);

这样,您只使用您知道其价值的数据。
变量重用
除此之外,您的变量名会导致一系列“代码混乱”。如果必须使用变量名的泛型,则会发生这种情况。一些例子:

$db = mysqli_select_db(...)
   while($db = mysqli_fetch_row($set1)){  //overwriting db
     ...
   }
   //....................
   while ($row = mysqli_fetch_array($set1)) {
     $colu[0] = $row["echo $colu[0]"]; //overwriting $colu

最后一个也是错误的,因为行键类似于:

$colu[0] = $row["echo name"];

或者有列名的东西。因为您在下一个循环中使用这个变量(“变量混淆”),所以它将是 $row["echo $colu[0]"]; 会被放回去的。所以我们假设这是正确的,没有 echo 并将使用 Name 作为值。

//loop 1
   $colu[0] = 'name';
   $row['name'] = 'Tom';
   //result 
   $colu[0] = 'Tom'

//loop 2
   $colu[0] = 'Tom';
   $row['Tom']  doesn't exist.
   //result 
   $colu[0] = null; //undefined index warning

游标重用
您还可以重用db游标 $set1 在上面绕了两圈。我不确定mysqli,但是pdo不允许你这么做。这可能就是第二个循环失败的原因。我认为第二个应该是 $mysqli_result . 这有点让人困惑,因为两个查询都是先循环一个然后循环另一个。而不是做一个查询,循环通过它。然后做另一个,然后循环。
相反,你可以这样做:

//you can even query the DB for the table names
$tables = ['user', 'user_meta', 'states']; //etc

$base =  !empty($_POST["base"]) && false !== ($index = array_search($_POST["base"], $tables)) ? $tables[$index] : false;

if(!$base) die('Unknown table '.$_POST["base"]);

$connection = mysqli_connect("localhost","login","pass") or die("Impossible to connect to the database!");
$db = mysqli_select_db($connection, "database")or die("Impossible to download the database!");

//---------query for the columns
$sql = "SHOW COLUMNS FROM `$base`";
$mysqli_result = mysqli_query($connection, $sql);

$columns = [];
while($row = mysqli_fetch_row($mysqli_result)){
    $columns[] = $row[0]; 
}

//---------query for the data
//use the column result in the select part of query, because the column names
//come from the DB they are safe to use.
$sql = "SELECT `".implode('`,`', $columns)."` FROM `$base`"; //reuse sql (no longer needed)

$mysqli_result = mysqli_query($connection, $sql); //reuse results (no longer needed)

//fetch all data as assoc array. because we tied it to the results 
//of the first query, the column names.  We no longer need to map it.
$data = mysqli_fetch_all($mysqli_result, MYSQLI_ASSOC);

///output table and headers

echo "<table>";
   echo "<thead>";
      echo "<tr>";
      //we can just loop over the columns and put them in the table head
      foreach($columns as $key ){
          echo "<th>$key</th>";
      }
      echo "</tr>";
   echo "</thead>";
   echo "<tbody>";

   //loop over each row of data
   foreach($data as $row){
      echo "<tr>";

      //loop over each "correlated" column
      foreach($columns as $key ){
         echo "<td>{$row[$key]}</td>";
      }
      echo "</tr>";  
   }
   echo "</tbody>";
echo "</table>";

从数据库获取表名的奖励:

$sql = 'SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE "'.$database.'"';
$mysqli_result = mysqli_query($connection, $sql);
$tables = mysqli_fetch_all($mysqli_result, MYSQLI_NUM);

希望这有道理。

相关问题