Honesty Rocks! truth rules.

Sql Query Mistake?

HOME      >>       Programming

alex1985

Hi, everyone! What is the mistake over here?

'id' INT(11) NOT NULL AUTO_INCREMENT,
'username' VARCHAR(255) NOT NULL,
'password' VARCHAR(255) NOT NULL,
PRIMARY KEY ('id')
) TYPE MYISAM; linenums:0'>CREATE TABLE '1_users' ('id' INT(11) NOT NULL AUTO_INCREMENT,'username' VARCHAR(255) NOT NULL,'password' VARCHAR(255) NOT NULL,PRIMARY KEY ('id')) TYPE MYISAM;
The PHPMyAdmin states:

#1064 - 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 ''1_users' ('id' INT(11) NOT NULL AUTO_INCREMENT,
'username' VARCHAR(255) NOT ' at line 1


Please, let me know as soon as possible?

rvalkass

This is a simple case of the wrong quotes. You need to use ` rather than '. The following should work:

CREATE TABLE `1_users` (`id` INT(11) NOT NULL AUTO_INCREMENT,`username` VARCHAR(255) NOT NULL,`password` VARCHAR(255) NOT NULL,PRIMARY KEY (`id`)) TYPE MYISAM;


alex1985

Oh, so easy!!! Thanks for answering, it's good to learn through your forum, replies got answered so fast.


alex1985

Sorry, but the PHPMyAdmin still gives the following:

SQL query:
CREATE TABLE `users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ;

MySQL said: Documentation
#1064 - 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 '' at line 2



rvalkass

OK, try the following:

CREATE TABLE `1_users` (`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`username` VARCHAR( 255 ) NOT NULL ,`password` VARCHAR( 255 ) NOT NULL) ENGINE = MYISAM

If that doesn't work, add the database name before the table name, so the first line would become:

CREATE TABLE `databasename`.`1_users` (


alex1985

So, I need to make spaces between words and brackets?! Correct me, I I'm being wrong!


rvalkass

Well, I just ran that code on my own MySQL installation and it worked perfectly. I have found in the past that it can be a little picky about what it lets through and what it doesn't.

 

If that code still doesn't work, use phpMyAdmin to create the table, and copy the MySQL query it actually runs (it will be displayed to you after the table is created). Then you can be certain that code will work.


alex1985

Well, I just ran that code on my own MySQL installation and it worked perfectly. I have found in the past that it can be a little picky about what it lets through and what it doesn't.

 

If that code still doesn't work, use phpMyAdmin to create the table, and copy the MySQL query it actually runs (it will be displayed to you after the table is created). Then you can be certain that code will work.


What's about my previous question?

truefusion

So, I need to make spaces between words and brackets?

Spaces (and new lines) are optional. It should work so long as you follow the syntax, like the error says. SQL query parse errors can be annoying.

alex1985

Thank you. I got it. If I have any more questions I let you know.


galexcd

This is a simple case of the wrong quotes. You need to use ` rather than '. The following should work:

Thats odd... I've never known that before. I've sometimes used ` but usually I use ' and they both seem to run fine on sql. Are you sure about this?

rvalkass

Thats odd... I've never known that before. I've sometimes used ` but usually I use ' and they both seem to run fine on sql. Are you sure about this?

I've found that sometimes it makes a difference. If I remember correctly, the difference is something similar to single quotes and double quotes in PHP. If you use a single quote in SQL, then whatever is between the single quotes will be parsed. If you use backticks (that's the ` character) then it is not parsed. This allows you to use words like INT in column names, which would otherwise be parsed by MySQL, throwing up an error as it would make no sense.

alex1985

I found one tutorial how to make a complete login system which suggest to use the following SQL syntax to encrypt the password:

INSERT INTO login (username,password,email,activated) value ('admin',sha1(concat('yourpasswordhere','0dAfghRqSTgx')),'youremailhere','1');

Is it appropriate or not?!


jlhaslip

Looks good to me. Try it on a test database and let us know if it works.What it is saying is to Insert into this table, the list of fields listed inside the first round brackets, the list of values found in the second set of round brackets. The list of fields and their values should be in the same order.The 'password' will be an encrypted value of the concatenated literal password and a 'salt'. This is more secure than simply encrypting the password by itself, but the same 'salt' should be used when you compare any passwords they use on log-in, so it needs to be stored someplace.


alex1985

Well, I just wanna ask you if it's good or appropriate way to encrypt the password. I checked the entry on PHPMyAdmin, and it does really encrypt the password. So, I can do that?!


galexcd

Well, I just wanna ask you if it's good or appropriate way to encrypt the password. I checked the entry on PHPMyAdmin, and it does really encrypt the password. So, I can do that?!

Well I'm not quite sure if sha1() is an sql function but I may be wrong, but it is a php function. And yes sha1 is a good encryption to use for encrypting passwords. It is similar to md5 but the hash is longer.

rvalkass

Well, I just wanna ask you if it's good or appropriate way to encrypt the password. I checked the entry on PHPMyAdmin, and it does really encrypt the password. So, I can do that?!

SHA1 is a good way to encrypt passwords at the moment. You would also be wise to 'salt' the passwords. This is a way to prevent against dictionary attacks. Many people are still stupid enough to pick a single dictionary word as a password. That is incredibly insecure, even if the password is hashed.

 

A good idea is to 'salt' the password. That simply means adding some random values to the end of the password, before hashing it and saving it in the database. To do this, when the password is created, also create some random data using the rand() function, or something similar. Put this random value on the end of the user's password, then hash it, then put it in the database. You also need to store the random data in the database!!!

 

Then, to check the password entered by a user, take their username, and pull the relevant piece of random data, along with the hash for their password from the database. Tag the stored random data onto the end of the password they entered, and hash that whole string. If that matches the one in the database then the password they entered is correct.

 

It can seem a little complicated at first, but it is much more secure than just hashing a user-entered password.


jlhaslip

A good idea is to 'salt' the password

Check the code that is posted above. It is using a salt value already.

INSERT INTO login (username,password,email,activated) value ('admin',sha1(concat('yourpasswordhere','0dAfghRqSTgx')),'youremailhere','1');



rvalkass

Check the code that is posted above. It is using a salt value already.

INSERT INTO login (username,password,email,activated) value ('admin',sha1(concat('yourpasswordhere','0dAfghRqSTgx')),'youremailhere','1');


Personally I prefer a random salt rather than a fixed one. A fixed salt requires one edited dictionary file. Whereas a system with a different hash for each person requires an entirely separate dictionary, and corresponding hashes, for each user.

alex1985

Thanks you guys. I will try to use yours, it seems better for me.