在查询中放置字符串时出现解析错误

Question

这是我的php代码。当我试图执行代码时，浏览器正在生成一个警告。

<?php
include("config.inc.php");

$link = mysql_connect('localhost', 'root', '')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully'; 
mysql_select_db('lookup') or die('Could not select database');
$query = 'SELECT airport_id FROM airport';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
echo "<table>n"; 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
 echo "t<tr>n";
 foreach ($line as $col_value) 
 {
   echo "tt<td>$col_value</td>n";
   $query1= 'SELECT distinct c.airport_ident,c.star_ident,c.fix_ident from corept.std_star_leg c
     INNER JOIN
     (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
      FROM corept.std_star_leg
      WHERE data_supplier='J' AND airport_ident='$col_value'
      GROUP BY star_ident,transition_ident)b
      ON c.sequence_num=b.seq AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
      WHERE  c.data_supplier='J'  AND c.airport_ident='$col_value'  AND
      NOT EXISTS
     (SELECT name,trans FROM skyplan_deploy.deploy_stars d
      WHERE d.apt=$col_value AND d.name!=d.trans
      AND c.star_ident=d.name and c.fix_ident=d.trans)
      UNION
     SELECT apt,name,trans FROM skyplan_deploy.deploy_stars d WHERE apt='$col_value' AND name!=trans
     AND
     NOT EXISTS
    (SELECT distinct c.star_ident,c.fix_ident from corept.std_star_leg c
     INNER JOIN
     (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type FROM corept.std_star_leg
      WHERE data_supplier='J'  AND  airport_ident='$col_value'
      GROUP BY star_ident,transition_ident)b
      ON c.sequence_num=b.seq  AND  c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
      WHERE c.data_supplier='J' AND  c.airport_ident=$col_value AND d.name=c.star_ident  AND d.trans=c.fix_ident)';
   $result1 = mysql_query($query1) or die('Query failed: ' . mysql_error());
  while ($line1 = mysql_fetch_array($result1, MYSQL_ASSOC)) 
  {
   echo "t<tr>n";
   foreach ($line as $col_value) 
    {
     echo "tt<td>$col_value</td>n";
     echo "t</tr>n";
    }
  }
 echo "t</tr>n";
}}
echo "</table>n";
 mysql_free_result($result);
 mysql_close($link);
?>

当我试图执行上面的代码时，它在第22行生成一个警告，其中我将$col_value包含到我的查询中。请帮我改正一下。谢谢你。

Answer 1

您缺少连接(.)操作符:

$str = "Part 1" . $value . "Part 2";

但是，请不要这样查询，而是使用参数绑定。这样的代码导致SQL注入攻击和/或轻微的性能下降。

Answer 2

使用此查询。问题是你用'开始你的字符串，并在变量周围使用'，但忘记转义它们。

$query1= "SELECT distinct c.airport_ident,c.star_ident,c.fix_ident from corept.std_star_leg c
 INNER JOIN
 (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
  FROM corept.std_star_leg
  WHERE data_supplier='J' AND airport_ident='$col_value'
  GROUP BY star_ident,transition_ident)b
  ON c.sequence_num=b.seq AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
  WHERE  c.data_supplier='J'  AND c.airport_ident='$col_value'  AND
  NOT EXISTS
 (SELECT name,trans FROM skyplan_deploy.deploy_stars d
  WHERE d.apt=$col_value AND d.name!=d.trans
  AND c.star_ident=d.name and c.fix_ident=d.trans)
  UNION
 SELECT apt,name,trans FROM skyplan_deploy.deploy_stars d WHERE apt='$col_value' AND name!=trans
 AND
 NOT EXISTS
(SELECT distinct c.star_ident,c.fix_ident from corept.std_star_leg c
 INNER JOIN
 (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type FROM corept.std_star_leg
  WHERE data_supplier='J'  AND  airport_ident='$col_value'
  GROUP BY star_ident,transition_ident)b
  ON c.sequence_num=b.seq  AND  c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
  WHERE c.data_supplier='J' AND  c.airport_ident=$col_value AND d.name=c.star_ident  AND d.trans=c.fix_ident)";

Answer 3

你用错引号了。请尝试下面的查询

$query1= "SELECT distinct c.airport_ident,c.star_ident,c.fix_ident from corept.std_star_leg c
         INNER JOIN
         (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
          FROM corept.std_star_leg
          WHERE data_supplier='J' AND airport_ident='$col_value'
          GROUP BY star_ident,transition_ident)b
          ON c.sequence_num=b.seq AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
          WHERE  c.data_supplier='J'  AND c.airport_ident='$col_value'  AND
          NOT EXISTS
         (SELECT name,trans FROM skyplan_deploy.deploy_stars d
          WHERE d.apt=$col_value AND d.name!=d.trans
          AND c.star_ident=d.name and c.fix_ident=d.trans)
          UNION
         SELECT apt,name,trans FROM skyplan_deploy.deploy_stars d WHERE apt='$col_value' AND name!=trans
         AND
         NOT EXISTS
        (SELECT distinct c.star_ident,c.fix_ident from corept.std_star_leg c
         INNER JOIN
         (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type FROM corept.std_star_leg
          WHERE data_supplier='J'  AND  airport_ident='$col_value'
          GROUP BY star_ident,transition_ident)b
          ON c.sequence_num=b.seq  AND  c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
          WHERE c.data_supplier='J' AND  c.airport_ident=$col_value AND d.name=c.star_ident  AND d.trans=c.fix_ident)";

Answer 4

将查询替换为:

$query1= 'SELECT distinct c.airport_ident,c.star_ident,c.fix_ident from corept.std_star_leg c
     INNER JOIN
     (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
      FROM corept.std_star_leg
      WHERE data_supplier="J" AND airport_ident='.$col_value.'
      GROUP BY star_ident,transition_ident)b
      ON c.sequence_num=b.seq AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
      WHERE  c.data_supplier="J"  AND c.airport_ident='.$col_value.'  AND
      NOT EXISTS
     (SELECT name,trans FROM skyplan_deploy.deploy_stars d
      WHERE d.apt=$col_value AND d.name!=d.trans
      AND c.star_ident=d.name and c.fix_ident=d.trans)
      UNION
     SELECT apt,name,trans FROM skyplan_deploy.deploy_stars d WHERE apt='.$col_value.' AND name!=trans
     AND
     NOT EXISTS
    (SELECT distinct c.star_ident,c.fix_ident from corept.std_star_leg c
     INNER JOIN
     (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type FROM corept.std_star_leg
      WHERE data_supplier="J"  AND  airport_ident='.$col_value.'
      GROUP BY star_ident,transition_ident)b
      ON c.sequence_num=b.seq  AND  c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
      WHERE c.data_supplier="J" AND  c.airport_ident=$col_value AND d.name=c.star_ident  AND d.trans=c.fix_ident)';

Answer 5

我认为错误在于您的查询字符串。试试这个:

$query1= "SELECT distinct c.airport_ident,c.star_ident,c.fix_ident from corept.std_star_leg c
     INNER JOIN
     (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
      FROM corept.std_star_leg
      WHERE data_supplier='J' AND airport_ident='".$col_value."'
      GROUP BY star_ident,transition_ident)b
      ON c.sequence_num=b.seq AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
      WHERE  c.data_supplier='J'  AND c.airport_ident='".$col_value."'  AND
      NOT EXISTS
 (SELECT name,trans FROM skyplan_deploy.deploy_stars d
  WHERE d.apt=$col_value AND d.name!=d.trans
  AND c.star_ident=d.name and c.fix_ident=d.trans)
  UNION
 SELECT apt,name,trans FROM skyplan_deploy.deploy_stars d WHERE apt='".$col_value."' AND name!=trans
 AND
 NOT EXISTS
(SELECT distinct c.star_ident,c.fix_ident from corept.std_star_leg c
 INNER JOIN
 (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type FROM corept.std_star_leg
  WHERE data_supplier='J'  AND  airport_ident='".$col_value."'
  GROUP BY star_ident,transition_ident)b
  ON c.sequence_num=b.seq  AND  c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
  WHERE c.data_supplier='J' AND  c.airport_ident='".$col_value."' AND d.name=c.star_ident  AND d.trans=c.fix_ident)"

你不能只在查询中包含变量。并在将查询放到php上之前在后端测试它。