Errors querying a MySQL database - could someone help me?

[jackwh]

Hi guys,  :?

 

I'm trying to program a registration form for my latest project.

 

It uses MySQL to save the results to.

 

The code goes like this:

 

FILE: newuser.php

<?php

	$firstname = $_POST['firstname'];
	$lastname = $_POST['lastname'];
	$country = $_POST['country'];
	$email = $_POST['email1'];
	$email1 = $_POST['email1'];
	$email2 = $_POST['email2'];
	$password1 = $_POST['password1'];
	$password2 = $_POST['password2'];

	$registerdate = "CURDATE()";
	$dbversion = 1;
	$membertype = bronze;

// Connect to the database
include("mysql/connecttodb.php");		// Include the MySQL connector code

$query = "INSERT INTO user (firstname, lastname, country, email, password, registerdate, dbversion, membertype) VALUES ($firstname, $lastname, $country, $email, $password1, CURDATE(), $dbversion, $membertype)";// This is the query to INSERT the data

// Now we query this to the database
$result = mysqli_query($cxn,$query) or die ("Couldn't execute MySQL query. Contact webmaster@tweetle.in.");

// If $result = true, querying was successful. Else it failed.
if ($result = true)
	{
	echo "Signup complete. Thanks for using Tweetle.in! Now please Login to Tweetle.";

	// We finish off by closing the connection to the database
	include("mysql/closeconnection.php");
	}
else
	{
	echo "MySQL querying failed. Please contact webmaster@tweetle.in. Sorry!";
	// We finish off by closing the connection to the database
	include("mysql/closeconnection.php");
	}

?>

 

All the data is definitely successfully collected from the form, I know this because on the signup page it displays it all too.

 

The error that I'm getting says "Couldn't execute MySQL query. Contact webmaster@tweetle.in.". This appears to be displayed when we query it to the database:

 

<?php $result = mysqli_query($cxn,$query) or die ("Couldn't execute MySQL query. Contact webmaster@tweetle.in.");?>

 

So, is it a problem with my code, or is it a problem with my Query?

 

If it's me using the CURDATE() (current date) MySQL function to save into the Date-formatted field, then is it me using CURDATE() wrongly?

 

Thanks for your help guys!

 

MySQL is definitely connected correctly. Because:

 

FILE: connecttodb.php

<?php 

// This file (connecttodb.php) connects to the MySQL database. It is used as an include() in other scripts.

$mysqlhost = "localhost";				// Defines MySQL host
$mysqluser = "swimwir1_*****;		// Defines MySQL user
$mysqlpassword = "***************";		// MySQL password
$mysqldbname = "swimwir1_*****";		// MySQL database name

// The next line of code connects to the database. If it fails, it produces an error message.
$cxn = mysqli_connect($mysqlhost,$mysqluser,$mysqlpassword,$mysqldbname) or die ("Sorry, MySQL connection error - please inform webmaster@tweetle.in");

?>

 

It nevers produces an error message from this script, so it is definitely connection (I have ***ed out private info).

 

Thank you!  :-D

[Adam]

The mysql_error() function should tell you what's wrong.

[ninedoors]

Maybe I'm wrong but it looks like you are trying to enter strings into a database without quotes around them to mysql they are strings.  Your INSERT should be like this:

 

<?php
$query = "INSERT INTO user (firstname, lastname, country, email, password, registerdate, dbversion, membertype) 
                VALUES ('$firstname', '$lastname', '$country', '$email', '$password1', CURDATE(), $dbversion, '$membertype')";
?>

 

Also it doesn't look like you are using the mysql_real_escape_string or any other validation on the data you are collecting.  You are leaving yourself wide open to injection attacks. 

 

Are you just leaving the passwrods as text.  If so this is not a very good practice either.

 

Just my thoughts.

 

Nick

[jackwh]

Maybe I'm wrong but it looks like you are trying to enter strings into a database without quotes around them to mysql they are strings.  Your INSERT should be like this:

 

<?php
$query = "INSERT INTO user (firstname, lastname, country, email, password, registerdate, dbversion, membertype) 
                VALUES ('$firstname', '$lastname', '$country', '$email', '$password1', CURDATE(), $dbversion, '$membertype')";
?>

 

Also it doesn't look like you are using the mysql_real_escape_string or any other validation on the data you are collecting.  You are leaving yourself wide open to injection attacks. 

 

Are you just leaving the passwrods as text.  If so this is not a very good practice either.

 

Just my thoughts.

 

Nick

 

Thanks for your help Nick. I'm surprised I missed it, not having quotes around it! But thank you anyway, a novice's error I suppose

 

I am still beginning learning MySQL and PHP, and (thanks to you) have now finally finished my login/registration system. I have simply 'drafted' this system, I haven't taken security into consideration yet but that's what I'll be doing next.

 

Yes, the passwords are currently just text - what format would I save it as in the database?

 

And, mysql_real_escape_string - does this remove any characters or formatting from the inputs? I.E. to stop injections like " ' or t=t" for example, it removes all the symbols? Am I right?

 

Thank you for your help so far!

[947740]

I do not believe it removes all symbols.  It just escapes characters that could be used for injection.

[jackwh]

OK... so if I run this code over all the variables before they're put in the database:

<?php

$firstname 		= mysqli_real_escape_string($firstname);;
	$lastname 		= mysqli_real_escape_string($lastname);
	$country 		= mysqli_real_escape_string($country);
	$email 			= mysqli_real_escape_string($email);
	$email1 		= mysqli_real_escape_string($email1);
	$email2 		= mysqli_real_escape_string($email2);
	$password1 		= mysqli_real_escape_string($password1);
	$password2 		= mysqli_real_escape_string($password2);
	$registerdate 	= "CURDATE()";
	$dbversion 		= 1;
	$userversion 	= 1;
	$membertype 	= bronze;

?>

 

... it should stop injection attacks, at least basic ones anyway?

 

Also, I have now encrypted passwords stored in the database like this:

<?php

AES_ENCRYPT('$password1','$key_str')

?>

 

$password1 is the password the user inputs, $key_str is an 80-character password set in another file, that I have include() ed in the script. Is this all correct so far?

 

The passwords come out in the database like this: O² Ö×™Ìé`¶LhÎûÝ

 

I have not yet tried to decode them, but I'll be doing that next.

 

Also, I am having troubles getting mysql_error() to work.

 

When I change my code from this:

<?php $result = mysqli_query($cxn,$query) or die ("Couldn't execute MySQL query. Contact webmaster@tweetle.in.") ?>

 

To this:

<?php $result = mysqli_query($cxn,$query) or die ("Couldn't execute MySQL query. Contact webmaster@tweetle.in. Error: $mysqlerror") ?>

when the $mysqlerror variable has been set earlier on to equal "mysql_error($cxn)" (where $cxn is the name of my connection).

 

Could anybody tell me why this isn't working, and also, could someone confirm that what i've done so far is correct?

 

Thank you!

[ninedoors]

This is what I use to see errors for mysql but you will have to alter it because you are using mysqli.

 

$result = mysql_query($query) or die("Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

 

Where $query is just the query string.  I find it helps sometimes to see the query, especially when you are using variables all over the place.

 

What will the users be logging in to see?  I am not an expert on security because I use SMF software as my login system so I have never really looked to hard into password security.  I do know that you want to steer clear of md5 as I believe that is now very vulnerable to attacks.

 

Yes.  mysql_real_escape_string works like 947740 said.  If you are going to have a forum on your site a would strongly recommend SMF and just do what I do and use their system.  It is extremely easy to create custom pages and the security is top notch plus their community is really helpful for anything you would like to do.

 

Good luck with site.

 

Nick

[Richard]

Looks like you're using MySQLi, (MySQL Improved, an extension introduced in PHP 5) - so you'll need to use mysqli_error() instead of mysql_error().

 

<?php
$result = mysqli_query($cxn, $query);
if (!$result) {
       throw new Exception('Query error: ' . mysqli_error($cxn) . '<br />File: ' . __FILE__ . '<br />Line: ' . __LINE__);
}

 

or die() must die

[jackwh]

Ah, thanks guys for your help! I don't know what I'd do without you! 

 

OK, so mysqli_error is working now. A couple more questions then.

 

@ninedoors, I'm not using a forum or anything. I'm simply developing a web application for a project that I am working on, that needs a user login/out system - I want a custom made one, it doesn't need any integration with something like SMF.

 

Anyhoo, basically a couple more questions.

 

1. I have a field in my database called 'userversion', and as you can see from the code I supplied in my last post, it's set to 1. The field type is identical to dbversion in the database. When I do a test signup, the user field is populated with data, and dbversion is 1. However, userversion always stays at 0, even though it's specified as 1 in the PHP script?

 

Both DBversion and USERversion have identical MySQL properties - both INTs, with length/values of 50, not null or anything.

 

So my question is, how can I fix this?

 

2. MySQLI_real_escape_string isn't escaping it, or not apparently so anyway. I did a test signup, and in the email field I put the following:

 

"example@example.com \' or t=t --"

 

(without quotes. Not that I don't have any Regex to validate that's it's actually an email... yet).

 

Looking in phpMyAdmin, it shows this under the email column:

 

"example@example.com \' or t=t --"

 

Identical! Am I misinterpreting this, or isn't MySQLI_real_escape_string meant to remove the dodgy characters like \, ' and =? Or does it just 'temporarily' remove them, then replace them in the database (so they're not queried?).

 

Thank you SO much for the help so far1

[ninedoors]

Can you export your DB structure from phpmyadmin for me so I can see exactly what you have?  Just paste it here as I am assuming its not too big a DB.  How big can userversion and dbversion be, I mean could they ever be a number like 12546235152?

 

Your right in that mysql_real_escape_string doesn't remove anything from your strings all it does is escape it so it can't be used for injections.  So when you type in the string: "We don't like dogs\cats", it escapes the ' and \ so that in your database it is stored like this:

"We don\'t like dogs\\cats".  So you will have to remember to use stripslashes on all selects that are strings.  Otherwise they will look like they do in the database.

 

Nick

 

 

[jackwh]

Thanks ninedoors. It's OK, it appears that the userversion thing is fixed now (now idea how, don't know what I changed?!)

 

I've put the Database Structure here anyway, just in case anything is a bit doolally.

 

Sorry about being such a n00b but I wasn't too sure how to go about exporting this, so I did it twice

 

Here it is in XML:

 

<?xml version="1.0" encoding="utf-8" ?>
<!--
-
- phpMyAdmin XML Dump
- version 2.11.9.5
- http://www.phpmyadmin.net
-
- Host: localhost
- Generation Time: Jun 25, 2009 at 02:56 PM
- Server version: 5.0.77
- PHP Version: 5.2.6
-->

<!--
- Database: 'swimwir1_tweetle'
-->
<swimwir1_tweetle>
  <!-- Table user -->
    <user>
        <userid>5</userid>
        <firstname>Jack</firstname>
        <lastname>Webb-Heller</lastname>
        <country>United Kingdom</country>
        <email>jack.wh@example.com</email>
        <password>æ%{)Š½åžUé™</password>
        <registerdate>2009-06-24</registerdate>
        <lastlogin>0000-00-00</lastlogin>
        <twitteraccounts>0</twitteraccounts>
        <comments></comments>
        <dbversion>1</dbversion>
        <membertype>bronze</membertype>
        <userversion>0</userversion>
    </user>
    <user>
        <userid>14</userid>
        <firstname>Timmy</firstname>
        <lastname>Turnip</lastname>
        <country>Thailand</country>
        <email>example@example.com \' or t=t --</email>
        <password>l	ßkÑYþÌ㻡Ä×</password>
        <registerdate>2009-06-25</registerdate>
        <lastlogin>0000-00-00</lastlogin>
        <twitteraccounts>0</twitteraccounts>
        <comments></comments>
        <dbversion>1</dbversion>
        <membertype>bronze</membertype>
        <userversion>1</userversion>
    </user>
</swimwir1_tweetle>

 

Here's a picture of the table:

 

 

And this one of the structure:

 

 

If everything's OK, I'll mark this topic Solved and continue with development.

 

Thanks for your help!

[ninedoors]

Don't worry about it, asking questions is how we all learn.

 

Sorry jack, I should have been more specific, can you export the database as an SQL?  That will give me the structure.  To do that you need to be in the database base meaning you are looking at all the tables listed.  Click export and it will have on the left radio buttons that will have a bunch of options, choose SQL and then in the middle of the screen uncheck the DATA box so you will only export the structure.  At the bottom of the page uncheck the save as file checkbox(should be that way by default) and click export button (or whatever it is in the bottom right).

 

This will bring up a textbox with all the SQL to built your table in it.  Copy that and paste it here.

 

If you don't need any further help don't worry about it, but if you do i will try to normalize your DB for you, so your structure will be more sound.

 

Nick

[jackwh]

Hi Nick,

 

OK, here's the SQL export:

 

-- phpMyAdmin SQL Dump
-- version 2.11.9.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 25, 2009 at 03:24 PM
-- Server version: 5.0.77
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `swimwir1_tweetle`
--

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `userid` bigint(20) NOT NULL auto_increment,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `country` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(64000) NOT NULL,
  `registerdate` date NOT NULL,
  `lastlogin` date NOT NULL,
  `twitteraccounts` int(140) NOT NULL,
  `comments` varchar(140) NOT NULL,
  `dbversion` int(50) NOT NULL,
  `membertype` varchar(50) NOT NULL,
  `userversion` int(50) NOT NULL,
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

 

Like I said, a small database that I'm still testing with, but I don't want to proceed with something that'll be hard to change later. Thank you Nick and others for your help so far.

[ninedoors]

Hey Jack,

 

A few things I would do to your DB is just change some of the data types.  For example you have your userid column as a BIGINT(20), which means you can have 18,446,744,073,709,551,615 different users.  Now if this is the case I would like to buy into your site as you are going to be a very rich man.  You will almost always be fine with a MEDIUMINT which will cover you for more than 16 million users. 

 

Same thing with your twitteraccounts, you have INT(140), not even sure what number that is, but you may want to think about changing it to even a SMALLINT(5) which goes from ~-65000 to ~65000.  What is that column used to stored anyways?

 

You have your password column as a VARCHAR(64000), do you need a string with 64000 characters in it?

 

The comments column, if it is a user's comments, should be a TEXT.

 

Look at changing the dbversion as well as the userversion for the same reason as the twitteraccounts.

 

Rememebr to always try to use the smallest possible type you can, but also looking ahead at what you might need in it for in the future.  Do this will make your DB smaller and faster to query in general.  Let me know if you have any questions.

 

I'm not an expert on database design but just some advise from what I've learned.

 

 

Nick

[jackwh]

Thanks Nick, I've changed userid and twitteraccounts.

 

twitteraccounts is a number used to store the number of Twitter Accounts a user has. The web app I'm programming connects to the Twitter API and provides a tool for Twitter Users. I've managed to get the whole API bit working now, I'm just now working on the user area (login, registration, etc.).

 

The comments column isn't for user comments, it's for admin comments. But I've changed it to text anyways since that seems logical.

 

I mistyped the password column, that was meant to be 6400 - I can't really remember how I got it (something to do with multiplying the characters in the AES_ENCRYPT passkey and the number of letters in a password?) but I've changed this to 100 to be on the safe side.

 

dbversion and userversion are used for when I update the structure of the DB, or add any new fields. Basically, if I change the Database format I'll update for new signups DBVERSION from 1 to 2. I'll then run a query that pulls out all emails with DBVERSION of 1, and email them prompting them to update their data to fit into our new database. Makes sense?

 

Your help is invaluable to me, I've learnt so much already just from this forum! I bought "PHP and MySQL for Dummies" for £23, and already a couple of chapters in I've spotted several function mistakes! (It says mysqli_real_escape_string only needs one argument, which isn't true and generates an error!  )

 

Compared to other languages I've tried in the past (OPL on Psions from the 1990s  , Delphi, Visual Basic) PHP seems a lot more 'logical' than anything else. I'm looking forward to expanding my knowledge on it, so thank you.

[jackwh]

OK wait, another question! 

 

The field 'twitteraccounts' is used for storing the integer number of Twitter Accounts a user owns. However, I want the user to essentially be able to add Twitter accounts to their profile.

 

This would mean once the user had added details for one, they would then be stored to the database. After adding another account, this would be added to the database.

 

However, my problem is that I need this to be unlimited. If a user had 50 accounts (unlikely, but you never know!) then I'm not going to take the time to create 100 columns in the database (twitterusername1, twitterpassword1, twitterusername2... etc...).

 

How would I do this so that each time a new account was added, it would add another column automatically? I know there's an SQL query I can use to add another column, however this seems slightly inefficient. Experts! Do you know any better ways of doing this?

[ninedoors]

Hey Jack,

 

You should create another table called twitteraccounts with a structure similar to this,

 

CREATE TABLE `twitteraccounts` (
`id` INT NOT NULL AUTO_INCREMENT ,
`userid` MEDIUMINT UNSIGNED NOT NULL ,
`account_no` INT NOT NULL ,
`date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` ) ,
INDEX ( `userid` , `account_no` ) 
) ENGINE = MYISAM 

 

I put a date_added column in there so you would know when they added the twitter account.  Now you haev your users add as many accounts as they want.  This is the idea behind realtional databases.

 

To get all your info out you will have to look up how to use JOINS in SQL.  They are fairly straight forward in this case. 

 

I agree with your comments on php.  Because of how logical it is and forums like these it is a very easy language to use even if you know nothing about programming.

 

Nick

[jackwh]

OK Nick, I've put the SQL in and I have a nice shiny new table, 'twitteraccounts'.

 

However, I don't really get it. I want a user to be able to add the Username and Password of a Twitter account, which fields would this data be stored in in your table example? Because surely once I've added one, there wouldn't be any more fields for any more?

 

Sorry, hope that makes sense...

 

Also, what's the difference in this case between 'id' and 'userid'?

[jackwh]

Bump  ???

[jackwh]

OK, I think I get it, but before I start the code, could someone confirm if this is correct?

 

In Nick (ninedoors)'s example of a table, 'id' is the unique field. What I do, is check the user's 'userid', and match it to the userid field in the table twitteraccounts, to check the information?

 

Now, how would I go about adding multiple accounts? So, there would surely need to be more than one 'account_no' and password field?  ???

[bradh]

Hi Jack,

 

What would happen is, whenever a user creates a new twitter account, it would store the data in the twitteraccounts table, with the userid being the id linked to the specific user the accounts belong to.

 

So what you would do to extract the information from both tables which belong to one user, is use a join.

 

So you could have a query like this:

 

SELECT user.*, twitteraccounts.* FROM user INNER JOIN twitteraccounts ON twitteraccounts.userid = user.id WHERE user.id = 1

 

This would get all of the information from both tables, where the userid is equal to 1.

 

Hope this helps to illustrate the table relationships.

 

Brad

[jackwh]

Thanks bradh, that's helped clear it up a little bit for me.

 

But I'm still confused (sorry!). I want my users to be able to add unlimited Twitter accounts. So when a user adds a Twitter account, it writes their username and password (encrypted) to the twitteraccounts table. This fill's the username and password field. But how do you add another account? Surely there's no more fields to fill in with more information? I mean, once you've written the username and password to the database, if you were to write any more it would just get overwritten since there's not any other fields for additional username and passwords?

 

Please can someone explain this? I apologise for my n00biness! 

[bradh]

Ha ha no problem jack.

 

Let me try explain this. What you are trying to do is called a 'one-to-many` relationship in relational database design.

 

This means that one user (one entry) in the user table can have multiple accounts (multiple rows) in the twitteraccounts table. SO:

 

Everytime a user adds a twitter account, it will add a new row to the twitteraccounts table. So your first account in the twitteraccounts table will look like this:

 

id: 1

userid: 1

account_no:1

username: testuser

password: &^(#*@&(

date_added: 29-06-2009 16:06

 

and the second account added will look like this:

 

id: 2

userid: 1

account_no:2

username: testuser2

password: &^(#*@&(#@#@#

date_added: 29-06-2009 16:10

 

So BOTH of these accounts will have a userid of 1 and will belong to the user who has that id. This is called the foreign key, and is basically how these two tables relate to each other.

 

so user.id relates to twitteraccounts.userid.

 

I hope this makes some sense to you

[jackwh]

Aha, thank you so much bradh! I think I just about understand now, he he 

 

So, I'd query that to the database with this whole JOIN thingy?

[JJ2K]

Basically if somebody has more than one account, then you insert a new entry but with the same userid.

 

So you could get someones twitter accounts by querying the table by their userid.

 

If you need to return data from a couple of tables the yes you can join them together in a query