Inserting data to mysql not working

Question

 <? 
session_start();


 if(($connection = mysql_connect("localhost", "root", "")) == false)
  die ("Couldn't connect to database");

 if(mysql_select_db("Social", $connection) == false)
   die ("Couldn't select db");

 if (isset($_POST['username']) && isset($_POST['pass']) && isset($_POST['login'])){

$sql = sprintf("SELECT * FROM users WHERE username LIKE '%s' AND password LIKE '%s'", $_POST['username'],  $_POST['pass']);
$query = mysql_query($sql);

if (mysql_num_rows($query) == 0){

$error = "<br />Wrong Username or Password";}

 else{
 $_SESSION['user'] = $_POST['username'];
 header("Location: home1.php");
       }
    }


if (isset($_POST['register'])){

         $sql2 = sprintf("INSERT INTO Social.users (username, password) VALUES   (%s, %s)", $_POST['newUser'], $_POST['newPass']);
         $query2 = mysql_query($sql2);

     if (!$query2){
        print "Registration failed";
     }
     else{
    print "Registration sucessfull";
    }
}

?>

My program is not inserting any data into mySQL table. I know all the syntax is right, everything should work out fine. I double checked on the command that mySQL uses in order to enter data into the table. Why is this not working? My query2 should be successful, but idk why its not. Please help. Thanks

What does `mysql_error()` say? FYI, you are wide open to [SQL injections](http://stackoverflow.com/q/60174) — John Conde, Apr 18 '14 at 00:13
It says Unknown column, its looking for a column of the registration name. Not the name of the column. wth? — Can't see me, Apr 18 '14 at 00:41
The column to insert the data should be username and password, but the mysql_error() is saying that it can't find column name '$_POST['newUser']' $_POST['newUser'] is the new registration name, I dont know why it is searching for the wrong column. — Can't see me, Apr 18 '14 at 00:55

score 0 · Answer 1 · answered Apr 18 '14 at 00:44

to prevent sql injections, try mysqli or pdo

here is mysqli prepared statements version. However if you are trying to create user management system, I wouldn't recommend you do it. There are so many scripts which provide more security, http://www.usercake.com is a good user management system.

 session_start();
$db = new mysqli('localhost', 'root', 'password', 'database');

 if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

 if (isset($_POST['username'] && $_POST['pass'] && $_POST['login']))
 {
 $user_name = ''; //define these here. 
 $pass = '';

 $stmt = $db->prepare("select * from users where username = ? and password = ?");
 echo $db->error;//this will echo the error.
 $stmt->bind_param('ss', $user_name, $pass);
 $stmt->execute();
 $result = $stmt->get_result();//get rows

 if($result->num_rows < 1) //check if result is less than 1
{
$error = "<br />Wrong Username or Password";}

 else{
 $_SESSION['user'] = $_POST['username'];
 header("Location: home1.php");
}
}

if (isset($_POST['register'])){

$uname = $_POST['newUser'];
$pass = $_POST['newPass'];

if(empty($uname))
{
echo "Please enter your username.";
}
elseif(empty($pass))
{
echo "Please enter your password.";
}
else{
$stmt = $db->prepare("insert into Social.users (username, password) values (?,?)");
echo $db->error;//this will echo the error.
$stmt->bind_param('ss', $uname, $pass);
$stmt->execute();
echo "You have successfully registered.";
}
}

score 0 · Answer 2 · edited May 23 '17 at 12:11

The variables in the INSERT must be the username and password

$sql2 = sprintf("INSERT INTO Social.users (username, password) VALUES   (%s, %s)", $_POST['username'], $_POST['pass']);

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

Using PDO:

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Using mysqli:

$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 }

Font: How can I prevent SQL injection in PHP?

Inserting data to mysql not working

2 Answers2