Where is my syntax error in this select statement?

Question

I'm getting a syntax error back on a sql insert statement that confuses me. The insert actually happens, but the error is halting the rest of the process.

The server is running PHP 5.6.35, and I'm using mysqli 5.5.59. I have other inserts written in similar fashion that work as expected.

Server Side:

<?php
    $db = mysqli_connect('localhost', '******', '******', '******'); //Starred out for posting here

    $charName = $_POST['charName'];
    $metatype = $_POST['metatype'];
    $user_id = $_POST['user_id'];

    $sqlStatement = sprintf("insert into characters (name, metatype, user_id) values('%s','%s',%s);",
        mysqli_real_escape_string($db, $charName),
        mysqli_real_escape_string($db, $metatype),
        mysqli_real_escape_string($db, $user_id));

    echo $sqlStatement;

    $result = mysqli_query($db, $sqlStatement);

    if($result == false)
    {
        echo 'Error Inserting to Database: '.mysqli_error($db);
        http_response_code(500);
        return;
    }
    else
    {
        //Not getting here, more core that should run if no error
    }
?>

With the code echoing the sql string back, I get:

insert into characters (name, metatype, user_id) values('Test 45','human',5);

Followed by:

Error Inserting to Database: 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 ')' at line 1

Returning to the character list, the new character is included.

On the database columns, name and metatype are utf8 text types, while user_id is an integer.

Regarding the duplicate flag:

I fail to see how this is a duplicate of the linked question. I'm using a different method for creating the statement, and my statement actually runs without error in everything but mysqli (where it still technically runs). The answer given is the same: "just use prepared statements", which doesn't actually answer the question. I get that they are superior, but as far as I can see, the method I am using is not deprecated in php, and should still work.

Update:

Switching to a prepared statement also yields this error. Code:

$db = new mysqli('localhost', '******', '******', '******');

$charName = $_POST['charName'];
$metatype = $_POST['metatype'];
$user_id = $_POST['user_id'];

$sqlStatement = $db->prepare("insert into `characters` (`name`, `metatype`, `user_id`) values(?,?,?)");
$sqlStatement->bind_param("ssi", $charName, $metatype, $user_id);

$result = $sqlStatement->execute();

if($result == false)
{
    echo 'Error Inserting to Database: '.mysqli_error($db);
    http_response_code(500);
    return;
}

Response:

Error Inserting to Database: 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 ')' at line 1

have you noticed that you have not a space between values and ( ? Have you fixed it? — Lelio Faieta, Jun 26 '19 at 12:54
Instead of using sprintf for quoting, use [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php) to bypass quoting issues and prevent SQL injection. — aynber, Jun 26 '19 at 12:55
You are open to SQL injection. You'd better switch to PDO and prepared statements for your code — Lelio Faieta, Jun 26 '19 at 12:55
A space between "values" and "(" and removing the semi-colon don't change anything: I believe I tried that last night, but I tried again a mew moments ago to be sure. — Tylor Emmett, Jun 26 '19 at 13:01
Is this sort of thing common for sprintf? I can certainly look at prepared statements, it's just not something I've used yet (still a little new to php). — Tylor Emmett, Jun 26 '19 at 13:03
What makes you think that the problem is in any way related to `sprintf`? Try using the very query you've posted as an input to `mysqli_query` — Nico Haase, Jun 26 '19 at 13:04
Because the query does work; the data is being inserted. But I'm still getting this error. That's where it's throwing me; I've never had a sql statement actually execute if it has a syntax error. — Tylor Emmett, Jun 26 '19 at 13:10
If the above query is being inserted fine, but you're still getting the error, are you running any other queries anywhere else? Maybe you're looking at the wrong place for the error. — aynber, Jun 26 '19 at 13:20
I don't believe so. All the scripts I have that access the database return something, and I'm not seeing any other return data. I'm also not making any other requests at that point. — Tylor Emmett, Jun 26 '19 at 13:36
Your latest edit is highly unlikely to produce such outcomde. The syntax error would make prepare fail and the error you'd get would be something like calling bind_param on null. Could you please post the full code you tried. — Your Common Sense, Jun 26 '19 at 16:02
I've updated the prepared statement code to show the rest. Thanks! — Tylor Emmett, Jun 26 '19 at 16:21
Like it was said before, you are getting this error message from some other code. This is what your code gives to me (if I make an intentional error): "Uncaught Error: Call to a member function bind_param() on bool". It just cannot yield an error like one you posted. — Your Common Sense, Jun 26 '19 at 16:24
You're right. Issue was further down; I had copied the error handling block and didn't remember to change the error text. Ugh. Thanks for the help! — Tylor Emmett, Jun 26 '19 at 16:38
You may delete your unsolvable question because it will not be reopened. — mickmackusa, Jun 26 '19 at 22:20
@Your I guess the Reopeners didn't see the comment from the OP. ...Round #2. — mickmackusa, Jun 27 '19 at 19:46

score 0 · Answer 1 · answered Jun 26 '19 at 12:57

0

You should start using prepared statements instead of using sprintf and escaping seperated. Additionally, there might be reserved words in your query like name - try using backticks around column names to avoid any confusion. The query could look like this:

insert into `characters` (`name`, `metatype`, `user_id`) values('Test 45','human',5);

Finally, your question might not be related to PHP or MySQLi after all - try running the query in a MySQL shell first to check whether it is valid

answered Jun 26 '19 at 12:57

Nico Haase

11,420
35
43
69

I am certainly going to look at prepared statements; I was apparently learning off an old example, but I am familiar with the concept in other languages, so it's not a stretch to change. As far as backticks, they make no difference (same error coming back). The statement does work in phpMyAdmin (the gui I have for mysql on my webhost) without error, and as mentioned before, the data is being inserted in the code above. – Tylor Emmett Jun 26 '19 at 13:16
phpMyAdmin is not a good reference, as it is known to reformat queries that might be broken in some way - try to run a **raw** sql query – Nico Haase Jun 26 '19 at 13:20
1

I guess I needed an excuse to reinstall Putty on this machine anyway. Running in the cli yields: `mysql> insert into characters (name, metatype, user_id) values('Test 45','human',5); Query OK, 1 row affected, 21 warnings (0.00 sec)` – Tylor Emmett Jun 26 '19 at 13:30
And what happens if you run that single query, generated as you've posted it here, using `mysqli_query`? Does that trigger any warning? – Nico Haase Jun 26 '19 at 13:43
Modifying the above code to : `//$result = mysqli_query($db, $sqlStatement); $result = mysqli_query($db, "insert into characters (name, metatype, user_id) values('Test 45','human',5);");` Yields the same result. – Tylor Emmett Jun 26 '19 at 14:49
Have you tried whether backticks around the column names helps? – Nico Haase Jun 26 '19 at 15:16
I did when I posted my first response to your comment. – Tylor Emmett Jun 26 '19 at 15:19
The OP admits that the details provided in the post are incorrect. Effectively, your solution cannot help. https://stackoverflow.com/questions/56773308/where-is-my-syntax-error-in-this-select-statement?noredirect=1#comment100110250_56773308 A page with no upvoted answers is easier for the system to purge after it is closed. This is just housekeeping. – mickmackusa Jun 27 '19 at 19:45

Where is my syntax error in this select statement?

1 Answers1