Page 1 of 1

Keep failing on insert

Posted: Wed Jun 30, 2010 9:26 pm
by Balveda
Im kinda new to php and mysql, but have been trying to get a submission form working to add data to a mysql db.

Its hosted on a Hostgator server, and I've been on to them already to make sure there are no limitations on the account with regards the functionality I need.

With the code below it always returns MySQL Insertion Failure, so Im assuming the connect.php script and the form script are working as planned. Any idea's?

Thanks,

Balveda

Code: Select all

<?php
if (isset($_POST['submit'])) {
}

$char1 = $_POST['char1'];
$rank = $_POST['rank'];
$class = $_POST['class'];
$spec1 = $_POST['spec1'];
$spec2 = $_POST['spec2'];
$comments = $_POST['comments'];

$error = FALSE;
if ($rank == "none") {
$error = TRUE;
}
if ($class == "none") {
$error = TRUE;
}
if (isset($comments)) {
$comments = trim($comments);
$comments = strip_tags($comments);
}
if (isset($char1) && 
isset($rank) && 
isset($class) && 
isset($spec1) && 
isset($spec2) && 
isset($comments) && 
$error == FALSE) {
$process = TRUE;
} else {
$process = FALSE;
}

include ("connect.php");

$query = "INSERT INTO character VALUES ('','$char1','$rank','$class','$spec1','$spec2','$comments','now()')";
$q = mysql_query($query);

if (!$q) {
exit("<p>MySQL Insertion failure.</p>");
} else {
mysql_close();
//for testing only
echo "<p>MySQL Insertion Successful</p>";
}

?>

Re: Keep failing on insert

Posted: Wed Jun 30, 2010 9:46 pm
by mikosiko
assuming that your connection is working..

some advices:
a) surround the table name (character) with backticks in this way : `character` because that is a mysql reserved word and almost sure is causing the insert to fail.

b) modify this line in your code to control errors:
Balveda wrote:$q = mysql_query($query);
to this:

Code: Select all

$q = mysql_query($query) or die( mysql_error());
c) Is good practice to name each field in your INSERT... in this way:

Code: Select all

    INSERT INTO table_name (field1, field2,.... fieldn) VALUES (value1, value2, ... valuen)

Re: Keep failing on insert

Posted: Wed Jun 30, 2010 10:10 pm
by Balveda
I changes the insert lines to

Code: Select all

$query = "INSERT INTO 'character' (id,char1,rank,class,spec1,spec2,comments) VALUES ('','$char1','$rank','$class','$spec1','$spec2','$comments','now()')";
$q  = mysql_query($query) or die( mysql_error());
and get the 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 ''character' (id,char1,rank,class,spec1,spec2,comments) VALUES ('','test','Champi' at line 1

Re: Keep failing on insert

Posted: Wed Jun 30, 2010 10:34 pm
by mikosiko
you must use backticks ` to sourround character... this.... `character` ... is not the same than this... 'character'

Re: Keep failing on insert

Posted: Wed Jun 30, 2010 11:01 pm
by Balveda
mikosiko wrote:you must use backticks ` to sourround character... this.... `character` ... is not the same than this... 'character'
Thanks dude - I nearly asked where the backtick was on my kb :oops:

Still getting an error, but getting somewhere at least:

Column count doesn't match value count at row 1

I've tried takind id and '' out of the line, but still getting the error.

Re: Keep failing on insert

Posted: Thu Jul 01, 2010 12:22 am
by Balveda
I worked it out :D

Thanks for your help mikosiko

Re: Keep failing on insert

Posted: Fri Jul 02, 2010 3:42 am
by VladSun
Your code is vulnerable to SQL injection attacks.
Use mysql_real_escape_string() to escape *ALL* data you want to insert.

E.g.

Code: Select all

$char1 = mysql_real_escape_string($_POST['char1']);
$rank = mysql_real_escape_string($_POST['rank']);
...