SQL query is failing

Question

I'm trying to insert data into my database and this query is failing and I can't figure out why. I'm just getting the 'Error updating database' output.

Table details

name    varchar(80)      
sitename    varchar(80)      
pages   text         
colors  text         
navigation  text     
content text

PHP

<?php
$name = $_POST['name'];
$sitename = $_POST['sitename'];
$pages = $_POST['pages'];
$color = $_POST['colors'];
$navigation = $_POST['navigation'];
$content = $_POST['content'];
mysql_connect("localhost","csuwebdev","") or die ('Error: ' . mysql_error());
mysql_select_db("csuwebdev");
$query = "INSERT INTO draft (name, sitename, pages, colors, navigation, content) VALUES ( '".$name."',  '".$sitename."', '".$pages."', '".$color."', '".$navigation."', '".$content."')";
mysql_query($query) or die ('Error updating database');
echo "Submission received...";

?>

HTML

<form method="POST" action="processDraft.php">
Your name<br>
<input type="text" name="name"><br>
Name of website<br>
The pages that will comprise the website and a brief description of each. A minimum of 4 is required (Home, About, Contact, and Resume)<br>
<textarea rows="4" cols="100" name="pages">
</textarea><br>
What colors might you use for the background, content background, header text, and paragraph text?<br>
<textarea rows="4" cols="100" name="colors">
</textarea><br>
What will the navigation buttons look like, and where will they be placed?<br>
<textarea rows="4" cols="100" name="navigation">
</textarea><br>
Where will you put your main content for each page?<br>
<textarea rows="4" cols="100" name="content">
</textarea><br>
<input type="submit">
</form>

`name` is a reserved keyword, try with `\`name\`` instead (in the query), a good method when a query fails is to try it out on the mysql workbench or similar, also it will highlight the reserved words in blue. — aleation, Mar 26 '14 at 08:41
FYI: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Paul Dessert, Mar 26 '14 at 08:41
Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, Mar 26 '14 at 08:42
Instead of `die ('Error updating database')` replace `die (mysql_error())` and see what is the exact error. — Shankar Narayana Damodaran, Mar 26 '14 at 08:42
@ShankarDamodaran Yes, I've just tried right now and it's a reserved keyword xD — aleation, Mar 26 '14 at 08:43
@aleation, I don't find **name** [here](https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) — Shankar Narayana Damodaran, Mar 26 '14 at 08:44
@aleation , even i had used 'name' many times for column names, it never shown me any error. — sunny, Mar 26 '14 at 08:46
You are right, it's not reserved, although it's been shown highlighted as so in the mysql workbench, I swear, I wish I could paste you a screenshot here xD — aleation, Mar 26 '14 at 08:47
@ShankarDamodaran Lol I just found this, it seems to be a bug from MySQL Workbench with the words 'type', 'types' and 'name': http://bugs.mysql.com/bug.php?id=62775 I should update mine xDD — aleation, Mar 26 '14 at 08:55
@ShankarDamodaran I've just updated to the latest version (6.0.9), It's still displaying it as a reserved word xD — aleation, Mar 26 '14 at 09:10

Shankar Narayana Damodaran · Accepted Answer · 2014-03-26T09:04:27.100

3

Rewrite your code to

$conn=mysql_connect("localhost","csuwebdev","") or die ('Error: ' . mysql_error());
mysql_select_db("csuwebdev",$conn);

You need to pass the database connection to your mysql_select_db before making queries to your table.

This(mysql_*) extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. Switching to PreparedStatements is even more better to ward off SQL Injection attacks !

PDO Version of the above code. [Didn't test though]

$dbh =  new PDO('mysql:host=localhost;dbname=csuwebdev', 'username', 'password');
$stmt = $dbh->prepare("INSERT INTO draft (name, sitename,pages,colors,navigation) VALUES(?,?,?,?,?)");
$stmt->execute(array($name,$sitename,$pages,$color,$navigation));

edited Mar 26 '14 at 09:04

answered Mar 26 '14 at 08:46

Shankar Narayana Damodaran

68,075
43
96
126

bonus points if you help me rewrite this so it is secure from SQL injection. I'm having a hard time directly relating the shared link into the syntax I'll need for this code – CodeManiak Mar 26 '14 at 08:55
1

@CodeManiak, I can write it for you.. but _spoonfeeding_ is not going to help you on the log run. What say ? Just start using prepared statements and if you get any issues.. post here we are always there to help you out ! – Shankar Narayana Damodaran Mar 26 '14 at 08:59
I know but the example query is a select with a single item not an insert with 6 `$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?'); $stmt->bind_param('s', $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with $row }` so I'm having a hard time relating this example. – CodeManiak Mar 26 '14 at 08:59
1

@CodeManiak, See the modified answer. – Shankar Narayana Damodaran Mar 26 '14 at 09:04
I understand, you can literally just put multiple ? and then stack the variables in the execute call. Thank you for the additional time explaining this. – CodeManiak Mar 26 '14 at 09:05

SQL query is failing

1 Answers1

PDO Version of the above code. [Didn't test though]