partians partians - 2 months ago 6
MySQL Question

"MySQL server version for the right syntax to use near '' at line 1" error

UPDATED: MySQL 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 '' at line 1

is the full error ^

I am getting the same error each time, no matter how many times I have checked over the code, I just can't seem to find any errors in it, am I being blind and it being right infront of my face?

<?php

if( $_GET['id'] ) {

$id = $core->clean( $_GET['id'] );

$query = $db->query( "SELECT * FROM quests WHERE id = '{$id}'" );
$data = $db->assoc( $query );

$editid = $data['id'];

}

?>
<div class="mws-panel grid_8">
<div class="mws-panel-header">
<span class="mws-i-24 i-home">WAG1 BADGE</span>
</div>
<div class="mws-panel-body">
<form action="" method="post" id="addNews" class="mws-form">
<div class="mws-form-inline">

<?php

if( $_POST['submit'] ) {

try {

$badgeid = $core->clean( $_POST['badgeid'] );
$badgename = $core->clean( $_POST['badgename'] );
$roomlink = $core->clean( $_POST['roomlink'] );
$difficulty = $core->clean( $_POST['difficulty'] );
$author = $core->clean( $_POST['author'] );
$tutorial = $core->clean( $_POST['tutorial'] );
$time = time();

if( !$badgeid or !$badgename or !$roomlink or !$difficulty or !$author or !$tutorial ) {

throw new Exception( "All fields are required." );

}
else {

if( $editid ) {

$db->query( "UPDATE quests SET badgeid = '{$badgeid}', badgename = '{$badgename}', roomlink = '{$roomlink}', difficulty = '{$difficulty}', author = '{$author}', tutorial = '{$tutorial}' WHERE id = '{$editid}'");

}
else {

$db->query( "INSERT INTO quests VALUES (NULL, '{$difficulty}', '{$badgeid}', '{$badgename}', '{$roomlink}', '{$author}', '{$tutorial}' '{$user->data['id']}', '{$time}';" );

}

echo "<div class=\"mws-form-message success\">Success!<ul><li>Quest done innit</li></ul></div>";

}

}
catch( Exception $e ) {

echo "<div class=\"mws-form-message error\">";
echo "Error";
echo "<ul><li>";
echo $e->getMessage();
echo "</li></ul></div>";

}

}

?>

<?php

$query = $db->query( "SELECT * FROM quests_difficulty" );

while( $array = $db->assoc( $query ) ) {

if( $array['admin'] != '0' or ( $user->hasGroup( '4' ) or $user->hasGroup( '5' ) ) ) {

if( $array['id'] == $data['difficulty'] ) {

$difficulty[$array['id'] . "_active"] = $array['difficulty'];

}
else {

$difficulty[$array['id']] = $array['difficulty'];

}

}

}
echo "<div class=\"mws-form-row\">";
echo "<label>URL of Badge Image:</label>";
echo "<div class=\"mws-form-item clearfix\">";
echo $core->buildField( "text",
"required",
"badgeid",
"Title",
"Your article's name.",
$data['badgeid'] );
echo "</div></div>";

echo "<div class=\"mws-form-row\">";
echo "<label>Badge Name:</label>";
echo "<div class=\"mws-form-item clearfix\">";
echo $core->buildField( "text",
"required",
"badgename",
"Description",
"A short description of your article.",
$data['badgename'] );
echo "</div></div>";

echo "<div class=\"mws-form-row\">";
echo "<label>Room Link of Badge</label>";
echo "<div class=\"mws-form-item clearfix\">";
echo $core->buildField( "text",
"required",
"roomlink",
"Title",
"Your article's name.",
$data['roomlink'] );
echo "</div></div>";

echo "<div class=\"mws-form-row\">";
echo "<label>Room Link of Badge</label>";
echo "<div class=\"mws-form-item clearfix\">";
echo $core->buildField( "select",
"required",
"difficulty",
"Category",
"Your article's category.",
$difficulty );
echo "</div></div>";

echo "<div class=\"mws-form-row\">";
echo "<label>Room Link of Badge</label>";
echo "<div class=\"mws-form-item clearfix\">";
echo $core->buildField( "text",
"required",
"author",
"Title",
"Your article's name.",
$data['author'] );
echo "</div></div>";
echo "<div class=\"mws-form-row\">";
echo "<label>Room Link of Badge</label>";
echo "<div class=\"mws-form-item clearfix\">";
echo $core->buildField( "big_textarea",
"required",
"tutorial",
"Article",
"Your article for the site.",
$data['tutorial'] );
echo "</div></div>";




?>
</div>
<div class="mws-button-row">
<input type="submit" name="submit" value="Submit" class="mws-button red" />
<input type="reset" value="Reset" class="mws-button gray" />
</div>
</form>
</div>
</div>

<?php
echo $core->buildFormJS('addNews');
?>


Any help would be appreciated, thank you.

Answer

For one thing, it looks like there's a comma missing here in the INSERT statement, and there's a missing close paren and unnecessary semicolon here:

... '{$tutorial}' '{$user->data['id']}', '{$time}';"
//         ------^                         -------^

Add the required comma separator, and add the closing paren and remove the unnecessary semicolon:

... '{$tutorial}', '{$user->data['id']}', '{$time}')"
//         ------^                          -------^

On a much bigger, more important note: the code appears to vulnerable to SQL Injection.

Best practice is to use prepared statements with bind placeholders.

UPDATE quests
   SET badgeid     = ?
     , badgename   = ?
     , roomlink    = ?
     , difficulty  = ?
     , author      = ?
     , tutorial    = ?
 WHERE id = ?

If for some (unfathomable) reason it isn't possible to use prepared statements, then at a minimum, any potentially unsafe values incorporated into a SQL statement must be properly escaped.

(Maybe that's what the "$core->clean" function is supposed to be doing, but we haven't really a clue what that really does.

For use in SQL, the values don't need to be "cleaned". Values need to be "escaped", ala mysqli_real_escape_string.