This query is not posting to the database

Question

This is all the code. the error is this.

query failYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Title','',now(),'macbook-retina-wallpapers-hd.jpg','Content','Tag','4','Status'' at line 1

<?php

if(isset($_POST['create_post'])){

     $post_title = $_POST['title'];
     $post_author = $_POST['authror'];
     $post_category_id= $_POST['post_category_id'];
     $post_status = $_POST['post_status'];
     $post_image = $_FILES['image']['name'];
     $post_image_temp = $_FILES['image']['tmp_name'];
     $post_tags= $_POST['post_tags'];
     $post_content = $_POST['post_content'];
     $post_date = date('d-m-y');
     $post_comment_count = 4;

     move_uploaded_file($post_image_temp, "../images/$post_image");


     $query = "INSERT INTO posts(post_category_id, post_title, post_author, post_date, post_image, post_content, post_tags, post_comment_count, post_status)";

     $query .= "VALUES({$post_category_id},'{$post_title}','{$post_author}',now(),'{$post_image}','{$post_content}','{$post_tags}','{$post_comment_count}','{$post_status}') ";


     $create_post_query = mysqli_query($connection, $query);

     if(!$create_post_query){

        die ("query fail" . mysqli_error($connection));


     }




}

?>




<form action = "" method="post" enctype="multipart/form-data">


    <div class="form-group">
        <label for="title"> Title</label>
        <input type="text" class="form-control" name="title">
    </div>

    <div class="form-group">
        <label for="Category"> Category</label>
        <input type="text" class="form-control" name="category">
    </div>

    <div class="form-group">
        <label for="Author"> Author</label>
        <input type="text" class="form-control" name="author">
    </div>

    <div class="form-group">
        <label for="post-status"> Status</label>
        <input type="text" class="form-control" name="post_status">
    </div>

    <div class="form-group">
        <label for="post_image"> Image</label>
        <input type="file" name="image">
    </div>

    <div class="form-group">
        <label for="post_tags"> Tags</label>
        <input type="text" class="form-control" name="post_tags">
    </div>

    <div class="form-group">
        <label for="post_content"> Content</label>
        <textarea type="text" class="form-control" name="post_content" id="" cols="30" rows="10"></textarea>
    </div>


    <div class="form-group">
        <input class="btn btn-primary" type="submit" name="create_post" value="publish post">
    </div>



</form>

So I am running a query when you enter the fields inn the form and submit post to database. For some reason it is not working. If you have any suggestions it would be great.

if(isset($_POST['create_post'])){

     $post_title = $_POST['title'];
     $post_author = $_POST['authror'];
     $post_category_id= $_POST['post_category_id'];
     $post_status = $_POST['post_status'];
     $post_image = $_FILES['image']['name'];
     $post_image_temp = $_FILES['image']['tmp_name'];
     $post_tags= $_POST['post_tags'];
     $post_content = $_POST['post_content'];
     $post_date = date('d-m-y');
     $post_comment_count = 4;

     move_uploaded_file($post_image_temp, "../images/$post_image");


     $query = "INSERT INTO posts(post_category_id, post_title, post_author, post_date, post_image, post_content, post_tags, post_comment_count, post_status)";

     $query .= "VALUES({$post_category_id},'{$post_title}','{$post_author}',now(),'{$post_image}','{$post_content}','{$post_tags}','{$post_comment_count}','{$post_status}') ";


     $create_post_query = mysqli_query($connection, $query);
}

When you say 'It is not working', how is it not working? Do you get an error? What is the expected behaviour and what is currently happening? — Henders, Aug 17 '16 at 20:30
Tried `echo $query;`? You're not escaping/preparing any data so it's probably the data you're submitting breaking the query. [PHP manual on prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). [Prepared statements in Docs](http://stackoverflow.com/documentation/php/275/using-a-database/2685/preventing-sql-injection-with-parametrized-queries) — Machavity, Aug 17 '16 at 20:31
More information would be nice. Additionally, you have what may be a typo (you wrote authror instead of what I assume should be author). — floatingeye, Aug 17 '16 at 20:32
query failYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''work','',now(),'','w','work work work','4','workw')' at line 1 — Dave, Aug 17 '16 at 20:34
One of the joys of PHP's modern MySQL APIs is support for prepared statements, so a look at switching to those. But just before you, echo $query. It's sometimes revelatory. — Strawberry, Aug 17 '16 at 20:36
"work' First double quote then a single, so your statement is broken there — Tobias Hagenbeek, Aug 17 '16 at 20:54
Dave It has been a while and you did not select any answer. If a solution worked for you, select and upvote it. If not, at least let us know how you fixed the problem so we (who tried to help) can also learn from this issue. — BeetleJuice, Sep 06 '16 at 16:32

BeetleJuice · Answer 1 · 2016-09-06T16:30:36.907

Your problem is that the values you're trying to insert (most likely $post_content or $post_author if they include apostrophes ') include quotes that confuse the SQL parser because it cannot determine where each value ends.

You need to use parameterized queries and prepared statements. Not only is it much more secure, but it makes it impossible for the SQL parser to be confused about what are the exact values.

/* Put ? where the values would be. One for each value, and don't use quotes */
$query = "INSERT INTO posts ... VALUES (?, ?, ?...)";
$stmt = mysqli_prepare($connection, $query) or die (mysqli_error($connection));
/*bind the values to the ? parameters (replace 's' with 'i' for integer values)*/
/*each 's' or 'i' indicates the type of value to replace the matching ? */
$stmt->bind_param('iss..', $post_category_id, $post_title, $post_author...);

/*execute the query and abort on error*/
$stmt->execute() or die ($stmt->error);

//Success!

@Dharman 100% agree, for production code. During development and or debugging, I think it's fine. — BeetleJuice, Dec 16 '19 at 07:10

score 0 · Answer 2 · answered Aug 17 '16 at 20:52

Yeah several things are needed for us to help, please print your query to yourself and post it here, maybe all adding a export of your dB structure. I see you have a date in there but the format is not sql standard.

Escaping your values is most likely the issue, so an example with a actual values would make it testable for us.

Also I see no space between $query ending and $query.= starting, also something some myself versions cannot handle

This query is not posting to the database

2 Answers2