Trouble adding new user record to MySQL database

Question

Possible Duplicate:
why cant i insert data into my sql db with php

well, in a nutshell i am writting a login form that stores data into a database. my html and script should be working perfectly, without too much regards to security at this exact moment in time. but the problem i have is my php script WILL NOT update my sql db. everything checks out fine, all post variables are carried over it just will not insert them. im staring to think that the user(yes i am using root) does not have sufficient privelleges for some reason, so i was wondering how to go about setting up a new "admin user" if you will. im running WAMP on my windows 7 machine ifthat makes a huge difference.

my form:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Registration</title>
</head>

<body>

<form action="register.php" method="post" />
<p>Username:</p><input type="text" name="username" />
<p>Password:</p><input type="text" name="password" />
<p>Name:</p><p>First:<input type="text" name="fname" />Last:<input type="text" name="lname" /></p>
<p>Email:</p><input type="text" name="email" />
<input type="submit" />

</body>
</html>

my script:

<?php

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="login"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form 
$myusername=$_POST['username']; 
$mypassword=$_POST['password']; 
$myfname=$_POST['fname'];
$mylname=$_POST['lname'];
$myemail=$_POST['email'];



// To protect MySQL injection 
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);
$sql="SELECT * FROM $tbl_name WHERE username='$myusername'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){

// username taken
echo "Username already taken";
exit();
}

//protection against sql injection
if (get_magic_quotes_gpc())
{
function stripslashes_deep($value)
{
$value = is_array($value) ?
array_map(stripslashes_deep, $value) :
stripslashes($value) ;
return $value;
}
$_POST = array_map(stripslashes_deep, $_POST);
$_GET = array_map(stripslashes_deep, $_GET);
$_COOKIE = array_map(stripslashes_deep, $_COOKIE);
$_REQUEST = array_map(stripslashes_deep, $_REQUEST);
}

//insert form into DB members




'INSERT INTO members SET
username="' . $myusername . '",
password="' . $mypassword . '",
fname="' . $myfname . '",
lname="' . $mylname . '"
email="' . $myemail . '"';

session_register("myusername");
session_register("mypassword"); 
header("location:registersuccess.html");
?>

sql commands used to create table:

use login
>database changed
CREATE TABLE members(
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL
)
;

i can then add a user update from the comand line, but php is a no go, again using root acount with a password. the script does connect to the server, because it does succesfully check if the user all ready exists or not. if any more info is needed, please just ask.

fyi: stripslashes does not protect against sql injection. if anything, it can make an injection attack WORSE by removing (some) escapes that previous escape runs had added. Have you checked if your query works at all with a `$result = mysql_query(...) or die(mysql_error())` type construct? Never assume a query succeeded. — Marc B, Sep 14 '12 at 19:55
Is there any reason why you're going through the considerable trouble of doing this by hand instead of using a PHP framework like [CodeIgnighter](http://codeignighter.com/) or [CakePHP](http://cakephp.org/)? Not only will these help structure your application, avoiding simple problems like this, but they will [properly escape your SQL data](http://bobby-tables.com/php) which you have done here using `mysql_real_escape_string`, which is a bandage at best. At the very least, using `mysqli` would enormously simplify the escaping process by using `bind_param`. — tadman, Sep 14 '12 at 20:30

Stu · Accepted Answer · 2012-09-15T09:52:17.817

1) Firstly, don't use the old mysql_* functions, they are insecure and depreciated (see the red box here). Instead look at using PDO or MySQLi, they don't take long to learn and are, imho, much better in every regard, including ease of use once you're used to them.

2) Secondly, SET is mainly used for UPDATE commands, not commonly used for INSERT. An INSERT command should usually look something like;

INSERT INTO `tbl` (`column1`,`column2`) VALUES ('value1','value2');

See MySQL manual.

3) Thirdly, I'm not sure if the code has copied incorrectly but it doesn't look like you've assigned the following insert string to a variable, nor executed it?

'INSERT INTO members SET
username="' . $myusername . '",
password="' . $mypassword . '",
fname="' . $myfname . '",
lname="' . $mylname . '"
email="' . $myemail . '"';

(again see point 2. about the syntax).

4) Fourthly, indentation!! Makes for a lot easier reading and also debugging. Properly indented code can show you errors just by reading the code, before even starting proper 'debugging' procedure which can save you a LOT of time! :)

`INSERT INTO ... SET` is a valid MySQL method for inserting data, but hardly anyone uses it. — tadman, Sep 14 '12 at 20:32

score 0 · Answer 2 · answered Sep 14 '12 at 20:01

0

I don't see you insert something in your database, to run the insert you need to use the same mysql_query function you used in the select statement, also, the syntaxis of your insert is incorrect, check the MySQL Manual to know how it is.

answered Sep 14 '12 at 20:01

Rafael

2,827
1
16
17

so it should look like this?: "INSERT INTO members (username, password, fname, lname, email) VALUES ('$_POST[username]','$_POST[password]','$_POST[fname]','$_POST[lname ]','$_POST[email]')"; – user1669503 Sep 14 '12 at 20:07
**NO**. It should look like `INSERT INTO members (username, password, fname, lastname, email) VALUES (:username, :password, :fname, :lname, :email)` and then you make a bind call to associate values with those placeholders. What you're doing there is **extremely** dangerous. – tadman Sep 14 '12 at 20:33
$return= "INSERT INTO members ('username','password','fname','lname','email') VALUES ('$myusername','$mypasword','$myfname','$mylname','$myemail')"; if (!mysql_query($return,$con)) { die('Error: ' . mysql_error()); } sorry not sure how to use code tag in comments, but i believe this is what youre refering to yes? it is giving me an error Undefined variable: mypasword" – user1669503 Sep 14 '12 at 20:45

Trouble adding new user record to MySQL database

2 Answers2