Database Query failed error when inserting Row into Table

Question

I am getting Database query failed error while trying to insert a new row into a table. This table (pages) has a column (subject_id) referencing another table (subjects). I am passing the value of the of the subject_id from the url and it is passed on the form correctly. All the values seem to be passed correctly on the form using php, but i get error while i try to insert the row. The form submits to itself.

select_all_pages_by_subject($sid) is a function that selects all rows (pages) from the current subject (passed from the url). It works fine for the position field.

I suspect this error is probably a MySQL syntax error somewhere in my code, but i just cant seem to figure it out yet. I appreciate some help. Thank you.

Here is my code:

<div class="body_content">
<?php
    $sid = null;
if(isset($_GET["subject"])) {
    $sid = $_GET["subject"];
}
?>
<form action="create_page.php" method="post">
Menu Name:  <input type="text" name="menu" /> <br>
Position:   <select name="position">
                <?php 
                $new_page_query = select_all_pages_by_subject($sid);
                $page_count = mysqli_num_rows($new_page_query);
                for($count=1; $count<=($page_count + 1); $count++) {
                            echo "<option value=\"$count\">$count</option>";
                }
                ?>
            </select> <br>
Visible:<br>    
No <input type="radio" name="visible" value="0" />
Yes <input type="radio" name="visible" value="1" /> <br>
Subject ID: <input type="text" name="subject_id" value="<?php echo $sid; ?>" /> <br>
Content: <br> 
<textarea rows="5" cols="40" name="content"></textarea> <br>
<input type="submit" value="Create Page" name="submit" /> <br>
<a href="admin.php">Cancel</a> <br>
</form>
<?php
        if(isset($_POST['submit'])) {
        $menu_name = $_POST["menu"];
        $position = (int) $_POST["position"];
        $visible = (int) $_POST["visible"];
        $content = $_POST["content"];
        $subject_id = (int) $_POST["$sid"];
        $insert_query = "INSERT INTO pages (subject_id, menu_name, position,
                          visible, content) VALUES ({$subject_id},'{$menu_name}', {$position},
                          {$visible}, '{content}')";
        $page_insert = mysqli_query($connection, $insert_query);
        if($page_insert) {
            $_SESSION["message"] = "Page created successfully";
            redirect_to("admin.php");
        } else {
        $_SESSION["message"] = "Page creation failed";
        redirect_to("create_page.php?subject=$sid");
    }
}
?>
</div>

Edit: removed the WHERE statement

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Nov 13 '17 at 19:05
You can't `INSERT` with a `WHERE`. You're looking for `UPDATE ... WHERE` — aynber, Nov 13 '17 at 19:06
Instead of `Page creation failed`, use error reporting, http://php.net/manual/en/mysqli.error.php. It will tell you what is wrong. — chris85, Nov 13 '17 at 19:10

score 1 · Answer 1 · edited Nov 13 '17 at 19:15

1

The problem is INSERT cannot have a WHERE after it.

$insert_query = "INSERT INTO pages (subject_id, menu_name, position, visible, content) VALUES ({$subject_id},'{$menu_name}', {$position}, {$visible}, '{content}')";

edited Nov 13 '17 at 19:15

Jay Blanchard

34,243
16
77
119

answered Nov 13 '17 at 19:07

Ori a

314
1
8

1

Please, do not use improper formatting on questions or answers. – Jay Blanchard Nov 13 '17 at 19:11
i just removed the "where" part of the query. i still get the "Database query failed" error – Smart Systems Nov 13 '17 at 19:11
1

Use some real error reporting @ITProManager as suggested under the question. – Jay Blanchard Nov 13 '17 at 19:12
@ITProManager A quote in any of those fields also would cause an error. Parameterize the query and use error reporting. – chris85 Nov 13 '17 at 19:16
@Jay your answer solved one part of the problem so i voted it up instead of marking it as the answer – Smart Systems Nov 14 '17 at 13:56
It isn't my answer @ITProManager Did you ever get the actual error? – Jay Blanchard Nov 14 '17 at 13:57
– Smart Systems Nov 15 '17 at 05:32
@JayBlanchard mysqli_error() doesnt seem to be outputing any error on screen other than "database query failed" on the source code. – Smart Systems Nov 15 '17 at 05:36
Please do not dump code in comments as it is unreadable. Edit your original post to add any new information. – Jay Blanchard Nov 15 '17 at 12:34

Smart Systems · Answer 2 · 2017-11-15T06:34:30.330

So after some troubleshooting, i decided to separate the form and form processing into 2 different pages, then i realized the problem, in the form action, i did not specify the subject id in the URL since i was passing the id from the URL:

<form action="create_page.php" method="post">

should be:

<form action="create_page.php?subject=<?php echo $sid; ?>" method="post">

Edit: I have also noticed that the "Database query failed" error was being called on the Position form field where i was making a database connection on the "pages" table to pull the number of rows. So when the insert statement failed due to the absence of subject id from the url, php did not process the page past the position form field, it called the error on the field and stopped execution. When insert query fails, parts of the form are displayed on the screen (only the menu name field and the position field with empty values). When i tried to view source code for errors, it requested the page be reloaded again (felt like an infinite loop running or something)

Database Query failed error when inserting Row into Table

2 Answers2