MySQL Syntax Error on PHP page

Question

Hi I get this error message:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id']; ?>'' at line 1

When running this code:

    $id = isset($_GET['id']) ? $_GET['id'] : '';
    $sql="SELECT * FROM $tbl_name WHERE id='$id'";
    $result=mysqli_query($con, $sql);
    if (!$check1_res) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
    }

$rows=mysqli_fetch_array($result);
?>

<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong><?php echo $rows['topic']; ?></strong></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><?php echo $rows['detail']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>By :</strong> <?php echo $rows['name']; ?> <strong>Email : </strong><?php echo $rows['email'];?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>Date/time : </strong><?php echo $rows['datetime']; ?></td>
</tr>
</table></td>
</tr>
</table>
<BR>

<?php

$tbl_name2="forum_answer"; // Switch to table "forum_answer"
$sql2="SELECT * FROM $tbl_name2 WHERE question_id='$id'";
$result2=mysqli_query($con, $sql2)or die(mysql_error());
while($rows=mysqli_fetch_array($result2)){

?>

<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong>ID</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>Name</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><?php echo $rows['a_name']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Email</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_email']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Answer</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_answer']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_datetime']; ?></td>
</tr>
</table></td>
</tr>
</table><br>

<?php
}

$sql3="SELECT view FROM $tbl_name WHERE id='$id'";
$result3=mysqli_query($con, $sql3);
$rows=mysql_fetch_array($result3);
$view=$rows['view'];

// if have no counter value set counter = 1
if(empty($view)){
$view=1;
$sql4="INSERT INTO $tbl_name(view) VALUES('$view') WHERE id='$id'";
$result4=mysqli_query($con, $sql4);
}

// count more value
$addview=$view+1;
$sql5="update $tbl_name set view='$addview' WHERE id='$id'";
$result5=mysqli_query($con, $sql5);
mysql_close();
?>

<BR>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="add_answer.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td width="18%"><strong>Name</strong></td>
<td width="3%">:</td>
<td width="79%"><input name="a_name" type="text" id="a_name" size="45"></td>
</tr>
<tr>
<td><strong>Email</strong></td>
<td>:</td>
<td><input name="a_email" type="text" id="a_email" size="45"></td>
</tr>
<tr>
<td valign="top"><strong>Answer</strong></td>
<td valign="top">:</td>
<td><textarea name="a_answer" cols="45" rows="3" id="a_answer"></textarea></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="id" type="hidden" value="<?php echo $id; ?>"></td>
<td><input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

I can not figure this problem out. Need help. Thanks!

try to surround your $tbl_name with `\`` and see if that fixes it — Dimi, Apr 06 '17 at 19:35
Your code is vulnerable to SQL injection attacks. You should use [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) or [PDO](http://php.net/manual/en/pdo.prepared-statements.php) prepared statements as described in [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 06 '17 at 19:36
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. WHERE id=''' at line 1. — Daniel Smith, Apr 06 '17 at 19:40
I think the problem is in your form. It's sending PHP code as the value of the `id` input. — Barmar, Apr 06 '17 at 19:41
`$tbl_name` probably doesn't have value seeing `right syntax to use near '. WHERE` or there's something funky happening with that variable. — Funk Forty Niner, Apr 06 '17 at 19:43
Use `View Source` to look at the page with the form, it probably has something like `value=""` in it. — Barmar, Apr 06 '17 at 19:43
@Barmar [This comment by the OP...](http://stackoverflow.com/questions/43264261/trouble-with-a-query-the-message-is-as-shown-below#comment73597143_43264261) shows a different message. — Funk Forty Niner, Apr 06 '17 at 19:44
`if (!$check1_res)` that does nothing. You most likely meant `$result` unless that's not your full/real code. Too many unknowns in this question. — Funk Forty Niner, Apr 06 '17 at 19:44
The variable chek1res does not exist. But the error should be from your html form. Check your value attribute. Probably the php tag wasn't closed or wasn't opened — Rotimi, Apr 06 '17 at 19:46
$sql="SELECT * FROM ".$tbl_name." WHERE id='".$id."'"; then do a echo $sql; to see if the query is correct. — Peter, Apr 06 '17 at 19:47
I got this output: SELECT * FROM forum_questions WHERE id='' — Daniel Smith, Apr 06 '17 at 19:50
Yes you can @scaisEdge. You just cannot use a prepared statement for table and column names. — Jay Blanchard, Apr 06 '17 at 19:51
The id is blank - that is why you're getting an error. `$id = isset($_GET['id']) ? $_GET['id'] : '';` What URL are you using to access this code? Or, are you using a form where the `method="get"` or doesn't have a method at all? Add your form markup to the question. — Jay Blanchard, Apr 06 '17 at 19:53
`mysql_fetch_array()` and `mysql_error()` - do not intermix with the `mysqli_` connection you established; that is partly why your code is failing. You should have posted your entire code from the beginning. You've been given an answer now, see that. @DanielSmith — Funk Forty Niner, Apr 06 '17 at 20:23
then there's `INSERT INTO $tbl_name(view) VALUES('$view') WHERE id='$id'` - INSERT does not use a WHERE clause, that too is failing. Same goes for a similar query. And did you not open your code with ` — Funk Forty Niner, Apr 06 '17 at 20:24
@Barmar ^ 2 comments. Seen their edit also? In case you're interested that is. — Funk Forty Niner, Apr 06 '17 at 20:25
The HTML is invalid. You can't have `
` directly inside ``. The children of `` have to be `` or `` — Barmar, Apr 06 '17 at 20:35

Jpsh · Answer 1 · 2017-04-06T20:48:09.577

Likely you were getting an error resulting in sql injection and or that you were using single quotes around an integer. To safeguard against sql injection the best way is to use prepared statements, however prepared statements only work for fields, so the best practice when dynamically changing table names in a query is use a white list where you are checking a value against a white list and setting the table with a static value to prevent injection. Also it's ideal to validate $_GET and $_POST values using filter_input.

$id=filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false) {
    //filter failed
    die('id not a number');
}
if ($id === null) {
    //variable was not set
    die('id not set');
}

//white list table 
$safe_tbl_name = '';
switch($tbl_name){
    case 'Table1': 
        $safe_tbl_name = 'MyTable1';
        break;
    case 'Table2':
        $safe_tbl_name = 'MyTable2';
        break;
    default:
        $safe_tbl_name = 'MyDataTable';
};

$sql="SELECT * FROM `$safe_tbl_name` WHERE id=?";
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($result);
if (!$check1_res) {
     printf("Error: %s\n", mysqli_error($con));
     exit();
}

$rows = $stmt->fetch_all(MYSQLI_ASSOC);

EDIT

Also worth noting, you're using mysqli however you've used the following mysql functions that should be changed to their mysqli counter parts.

mysql_close()
mysql_error()
mysql_fetch_array()

EDIT

As @Barmar noted you're creating invalid HTML, I would strongly recommend running your output page through the W3C HTML Validator and correcting issue.

Yep that is great all errors are gone now, thanks a lot :D I got the output id not a number — Daniel Smith, Apr 06 '17 at 20:37

MySQL Syntax Error on PHP page

1 Answers1