php - MySQL syntax error while trying to upload information to database with a form -
i made recipe website php class. thing can't figure out how have user add own recipe. created form when hit submit button error "you have error in sql syntax; check manual corresponds mysql server version right syntax use near ''servings', 'image')values (null, 'oatmeal pancakes ii', 'i make kid' @ line 1" appreciate help! thank you!
<?php // make note of current working directory relative root. $directory_self = str_replace(basename($_server['php_self']), '', $_server['php_self']); // make note of location of upload handler $uploadhandler = 'http://' . $_server['http_host'] . $directory_self . 'upload.processor.php'; // set max file size html upload form $max_file_size = 30000; // size in bytes ?> <?php //include functions require_once('includes/functions.php'); ?> <!doctype html> <html> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <title>add recipe</title> <link href="images/style.css" rel="stylesheet" type="text/css"> </head> <?php $name = isset($_post['name']) ? $_post['name'] : ''; $description = isset($_post['description']) ? $_post['description'] : ''; $ingredients = isset($_post['ingredients']) ? $_post['ingredients'] : ''; $preparation = isset($_post['preparation']) ? $_post['preparation'] : ''; $category_id = isset($_post['category_id']) ? $_post['category_id'] : ''; $servings = isset($_post['servings']) ? $_post['servings'] : ''; $image = isset($_post['image']) ? $_post['image'] : ''; //connect database require_once('includes/mysqli_connect_recipe.php'); //if submit button clicked if(isset($_post['submit'])){ $valid = true; // require name, description, ingredients , preparation @ least 2 characters if(strlen($name) < 2){ $valid = false; echo "please provide valid recipe name.<br>"; } if(strlen($description) < 2){ $valid = false; echo "please provide valid description.<br>"; } if(strlen($ingredients) < 10){ $valid = false; echo "please provide valid ingredients.<br>"; } if(strlen($preparation) < 10){ $valid = false; echo "please provide valid instructions.<br>"; } //sanitize servings $servings = intval($servings); //force $servings number (0 if string entered) // sanitize against sql injections (do every field that's coming form) $name = mysqli_real_escape_string($dbc, $name); $description = mysqli_real_escape_string($dbc, $description); $ingredients = mysqli_real_escape_string($dbc, $ingredients); $preparation = mysqli_real_escape_string($dbc, $preparation); // sanitize against xss attacks - fields $description = strip_tags($description); $name = strip_tags($name); $ingredients = strip_tags($ingredients); $preparation = htmlspecialchars($preparation); if($valid){ // insert sql $insert = "insert `sburg5`.`recipes` (`recipe_id`, `name`, `description`, `ingredients`, `preparation`, `category_id`, 'servings', 'image')values (null, '$name', '$description', '$ingredients', '$category_id', '$servings', '$image');"; // execute insert query $result = mysqli_query($dbc, $insert) or die(mysqli_error($dbc)); echo "thank submitting recipe!"; // output recipe while($row = mysqli_fetch_array($result)){ echo "<h3>{$row['name']}</h3> <p><img src=\"data:image/jpeg;base64,' . base64_encode{$row['image']} . '\"></p> <p>" . $row['description'] . "</p> <p>" . nl2br($row['ingredients']) . "</p> <a href=\"addarecipe_edit.php?recipe_id={$row['recipe_id']}\">[edit]</a> <a href=\"addarecipe_delete.php?recipe_id={$row['recipe_id']}\">[delete]</a> <hr>"; } } } ?> <form id="upload" action="<?php echo $uploadhandler ?>" enctype="multipart/form-data" method="post"> <p> <label for="name">recipe name:</label> <input type="text" name="name" id="name" > </p> <p> <label for="servings">servings:</label> <input type="text" cols="50" name="servings" id="servings"> </p> <p> <label for="description">description:</label> <textarea rows="4" cols="50" name="description" id="description"></textarea> </p> <p> <label>type of recipe: <input type="radio" name="category_id" value="1" id="category_0" >main entree</label> <label> <input type="radio" name="category_id" value="2" id="category_1">appetizer</label> <label> <input type="radio" name="category_id" value="3" id="category_2" >side dish</label> <label> <input type="radio" name="category_id" value="4" id="category_3" >dessert</label> </p> <p> <label for="ingredients">ingredients:</label> <textarea rows="10" cols="50" name="ingredients" id="ingredients" placeholder="separate each ingredient return."></textarea> </p> <p> <label for="preparation">preparation:</label> <textarea rows="10" cols="50" name="preparation" id="preparation"></textarea> </p> <p> <input name="max_file_size" value="<?php echo $max_file_size ?>" type="hidden"> <label for="file">file upload:</label> <input id="file" type="file" name="file"> <p class="submit"> <input type="submit" name="submit" value="upload me!"> </form> </p> <?php // close connection database mysqli_close($dbc); ?>
the reason why query won't work because wrapping column name single quotes. identifiers , not string literals shouldn't wrap single quote.
insert recipes (`recipe_id`, `name`, `description`, `ingredients`, `preparation`, `category_id`, 'servings', 'image') ^ problem here ^ should backtick if happens column names and/or tables names used reserved keywords, can escape backticks not single quotes.
in case, backticks aren't required since none of them reserved keywords.
other links:
Comments
Post a Comment