Solved

MySQL - Check before insert

Posted on 2014-11-16
18
336 Views
Last Modified: 2014-11-17
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
Comment
Question by:projects
  • 9
  • 5
  • 4
18 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40445830
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
 

Author Comment

by:projects
ID: 40445840
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
 
LVL 58

Expert Comment

by:Gary
ID: 40445853
ALTER TABLE htaccess ADD UNIQUE KEY user_pass (username, password)

Open in new window

0
 
LVL 76

Expert Comment

by:arnold
ID: 40445859
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
 
LVL 76

Expert Comment

by:arnold
ID: 40445860
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
 

Author Comment

by:projects
ID: 40445862
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
 
LVL 58

Expert Comment

by:Gary
ID: 40445865
@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
 

Author Comment

by:projects
ID: 40445881
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
 
LVL 76

Accepted Solution

by:
arnold earned 250 total points
ID: 40445883
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 58

Assisted Solution

by:Gary
Gary earned 250 total points
ID: 40445894
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
 

Author Comment

by:projects
ID: 40446140
>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
 

Author Comment

by:projects
ID: 40446142
Interesting. I just noticed that when I ran this command, all of the username field was rearranged into alphabetical order.
0
 

Author Comment

by:projects
ID: 40448101
A little syntax change and it works fine now. Thanks.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40448159
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
 

Author Comment

by:projects
ID: 40448190
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
 

Author Comment

by:projects
ID: 40448256
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
 
LVL 76

Expert Comment

by:arnold
ID: 40448269
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
 

Author Comment

by:projects
ID: 40448286
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now