Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

MySQL - Check before insert

I am putting together a mod auth mysql setup for a web site.
Using a bash script, I enter the name and other details for the user/passwd but I need to check for two things before inserting.

This is my current command;

mysql -u test -ptest -D somedb -h 192.168.43.3 -e "insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE')"

I need a new command which does the following;

1- Check to see if that user exists
2- If user doesn't exist, then create a new record with name/password
3- If user exists, check to see if the password is the same, different or non existent
4- If non existent or different, then update
5- If the same, don't update
0
projects
Asked:
projects
  • 9
  • 5
  • 4
2 Solutions
 
GaryCommented:
Set a unique key on the username and password together

Change your sql to
insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE') ON DUPLICATE KEY UPDATE username='$NAME';

Open in new window


The unique key will prevent multiple entries and the ON DUPLICATE... will just update the username and carry on

You can also use ON DUPLICATE KEY IGNORE - but not really recommended as it just gives a warning and doesn't report any errors that have occured
0
 
projectsAuthor Commented:
My fields look like this at the moment;

Field name    	Type    	        Allow nulls?    	Key    	Default value
username 	varchar(45) 	No 	                Primary 	NULL 	
password 	char(45) 	        No 	                None 	NULL

Open in new window


I don't have any 'unique key' settings. Can you tell me how to do this so I do it properly. Then I will test.

I can do it via command line webmin or phpmyadmin.
0
 
GaryCommented:
ALTER TABLE htaccess ADD UNIQUE KEY user_pass (username, password)

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
arnoldCommented:
Gary's example is one, but he updated the wrong column

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

modified example of Gary's code

insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE') ON DUPLICATE KEY UPDATE password='$PASS_CODE';

Open in new window


Another option is to use if not exists select username from htaccess where username='$NAME' then insert else update
depending on the variations, you can use  case statement as well. ......
0
 
arnoldCommented:
The update of password even if the same, is better than going through verifying whether the password is the same.
It is a simple coparison when they are both plain, if they are hashed password('password')
0
 
projectsAuthor Commented:
Ok, added those but I guess I need the full syntax as I am messing it up.

mysql -u me -psomepass -D somedb -h 192.168.23.43 "insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE') ON DUPLICATE KEY UPDATE username='$NAME';"
0
 
GaryCommented:
@arnold
If the user/pass are the same then it doesn't matter which column is updated

What error are you getting projects and did you add the unique key
0
 
projectsAuthor Commented:
Yes, I added the unique key but my syntax was wrong in the bash script. I had to add -e.

Anyhow, it is creating the account just fine but never updates it and there are no errors.

The user has select, insert and update access.
0
 
arnoldCommented:
gary,
If the duplicate is triggered, it means the username exists as it is the primary, then you would update the password, no reason to update the username.

projects,

you can stop using the -D database_name
by using the insert into database_name.table_name VALUES ('$NAME','$PASS_CODE') on DUPLICATE KEY update password='$PASS_CODE';

If your table only has two columns, the order of values is important, but the column names can be left out.
0
 
GaryCommented:
Yeah I need more coffee!

The unique key should be on the username only not the password as well.
Run this

ALTER TABLE htaccess DROP INDEX user_pass, ADD UNIQUE KEY username (username)
0
 
projectsAuthor Commented:
>you can stop using the -D database_name

I'm not sure I can. The workstation and the server are across different networks. I am not running this on the local server so how would it know which database to use?

So, I added another record, then I ran the same command to change it's password but nothing changed and no errors.

mysql -u someuser -psomepass -D somedb -h 192.168.1.40 -e "insert into htaccess (username,password) VALUES ('$LOCATION','$PASS_CODE') ON DUPLICATE KEY UPDATE username='
$LOCATION';"

Open in new window

0
 
projectsAuthor Commented:
Interesting. I just noticed that when I ran this command, all of the username field was rearranged into alphabetical order.
0
 
projectsAuthor Commented:
A little syntax change and it works fine now. Thanks.
0
 
arnoldCommented:
there are multiple ways to achieve what you are looking for as well as some more complicated dealing with different behaviors depending on specific values.



The check should done before you even go through modifying mysql.  
are your intent is on sending a single command, and have all the needs satisfied?
0
 
projectsAuthor Commented:
My original quest was to read the DB, check for conditions, then insert/update as needed.
It's always nice to have smarter over potential problems of course.
0
 
projectsAuthor Commented:
It seems to be working fine. The only thing I'd like to do is to get rid of an error message

Before adding the on duplicate function, the script would display;

ERROR 1062 (23000) at line 1: Duplicate entry 'TC11054' for key 'username'

Now, instead, I would like the script to say 'Updated User TC11054 password' or 'Created User TC11054' as needed. But, that's not such a big deal.
0
 
arnoldCommented:
The only way to achieve informational data response, is to use selects, and then inserts or with case statements or use an stored procedure

Exec perform_user_taks
Where you run selects to see whether the user exists. If the user exists check the password, if non-existent or unmatched, update and then return the message string.
0
 
projectsAuthor Commented:
I kind of understand that but it's a little beyond me. That's why I'm on this site looking for the actual solutions until I can hire someone to take care of all this :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now