MYSQL :: Transferred DB, Now Passwords Not Working..

Passwords
1. I know in mySql you need a username and a password to access a database. However, can you set a username and password (or even just a password) to access a table or a row?

2. Is there a way you can set a database so you can only add data?

3. is there a way I can have people access values to a database (php) without using a password?

4. is there a way to set up diffrent users (with diffrent permissions) for accessing mySQL data?

5. Any comments about how I can get alot of people accessing a database, from diffrent websites, with keeping i as secure as possible?

Passwords
i've written a windows based from end for a mysql database that will work across a local intranet, it stores usernames and passwords for a reporting system, what is the best way to store the passwords int he db? i.e encrypted or?

Passwords & DBI/DBD
I installed the current stable release of MySQL from RPMs. I used the client
and server packages.

After installing the server package, the RPM printed a note saying I should
run two commands. I only got the first one, which was
# ./mysqladmin -u root --password
I assume this gave the root user a password.

I then downloaded and proceeded to compile the Perl DBI and DBD:mysql
packages. The DBI package installed fine after I also installed the
"MySQL-devel" RPM. Code:

Passwords
I want to store some passwords in a MySQL table. Is there any way of
preventing someone using say COntrolcentre from viewing the passwords.

Passwords
I am just starting out using mySQL. I created some databases and tables and
installed mySQLcc to ease my usage. After about 4 months, I've needed to
make some changes that needed root access. I appearantly wrote down the
wrong password so I couldn't make any changes. In the process of trying to
change the password I think I changed it again and unsynced mySQL and
mySQLcc because now I can't access any of the databases with any user.
Although I can still open mySQLcc, it can't connect to the database. I get
access denied no matter what I do. Is there a way to clear the passwords
and start over? Where are the passwords kept? Or do I have to remove
mySQL and reinstall it and start over?

Transferring Passwords
I tried doing a search but did not find the answer I was looking for.

I just redesigned the database that I had created a few months ago. I broke my one single table up into multiple tables.
My problem is that in my old large table I had a password column that is populated by using Password(). Is there a way for me to transfer the info from that column to my new broken down table set?

I did not create a new database when I broke up the large table, I just built my new tables around it so they are all stored in the same database. I'm not sure if that makes a difference or not.

Is it possible for me to just copy and paste the info from one of the fields into the new table? Or will that break the encryption?

Passwords For Myadmin
I am looking for a way to setup a username/password to myadmin. I made the mistake of showing other the website for admin work and now they are messing it all up.

Inserting Passwords Into DB
I have a couple of questions concerning the insertion of encrypted passwords into a mysql database (4.0)
first, some admins seem to think it's a good idea to feed the password in plaintxt to the db and have the db encrypt and store it. I tend to think this is less secure seeing as there is no seperation between the data and the encrypt/decrypt

so therefore encrypt the password beforehand and I want to insert nand retreive it that way. What type should I be using with the store, and what's the best way to store and retrieve it with that type?

Passwords / Authentication
I have just started messing around with MySQL and something I find weird is that we connecting using localhost, if I supply the password, it will not authenticate...if I leave the password off, it's fine.If I connect from another machine, it requires the password or it will not authenticate.Is that normal?

How To Encrpyt Passwords In Database?
I'm making a user login system with PHP/MySQL & when users sign up & such I want their password put into the database & encrpyted... how do you do this?

Encripting Passwords In Mysql
Is there any way to encript a password in a mysql database, so that if anyone looks in the database, they wont know peoples passwords?

Encoding Passwords In MySQL
I am developing a database application in which I store usernames and
passwords. Naturally I want to store the passwords in an encrypted form.
However, just like you see in many web applications, I want to be able
to email the users password back to them should they forget their old
password.

I looked at how to store the password data in MySQL and they recommend
using MD5 or SHA. Both of these appear to be one way encryption
routines. IOW once I encrypt and store the users password there is no
way to unencrypt it.

How can I store passwords in my SQL database such that I can convert
them back to plain text when the user requests them?

Problem With (hashed) Passwords
I my program I have my own login form from which I forward username and password to (ADO)ConnectionString, and then make Connection Active.
But, MySQL Server reports error 'Client does not support authentication protocol requested by server; consider upgrading MySQL client'
Newest version of server (and client) is installed.
Help says that server uses hashed password when user is attemp to connect.
How can I provide hashed password from my application?

User Accounts With Passwords
I am able to connect to the database using users that are not password protected. After updating privileges to use passwords and changing login info(in a php document) I recieve the following error from the php document:

Warning: mysql_query(): Access denied for user 'ODBC'@'localhost' (using password: NO) in c:inetpubwwwrootgindex.php on line 9

Warning: mysql_query(): A link to the server could not be established in c:inetpubwwwrootgindex.php on line 9

I am attempting to connect as 'test'@'localhost' with the password 'test'. If I remove the password, I can connect no problem.

Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?

Security For Shared Server.xml Passwords
I have an ISP that has the server.xml in a shared environment. The server.xml file is where my password is available for database access under connection pooling. I really would like to use connection pooling.

Does anyone know how I can protect my password from being viewed by others using the same server.xml file? I cannot get the administrator to change the protection of the file because others in the shared environment need to be able to view it.

Hide Mysql Usernames And Passwords
I'm trying to have people access a secure mysql database. If they get to a certain webpage, they have clearance to access the database, and we have one password and one username (that the users do not know). Does anyone know how to have it so the people cannot see the cgi script to access the database?

User Passwords Lost On Reboot
Server 2003 | IIS6 | MySQL 4.1.14-nt-max | phpMyAdmin 2.6.4-pl3

Apart from root, I have only 6 databases and whenever I reboot the server, I have to reset the passwords on 4 db's (using phpMyAdmin). Checking the mysql db, user table I note that the 4 that are causing problems have an asterix *preceding the encrypted password. The passwords that do not change were set with the help of a friend using SQL statements.

Mod_auth_mysql Or Mysql Usernames And Passwords
I'm writing a web app that needs a login page. I'm doing the dev on a
windows box although the final version will go on a Linux box. I can't
find any versions of mod_auth_Mysql precompiled for windows and have no
idea about compiling c.source on windows (or anything to do it with).

Is there a good reason I shouldn't store hashed password in MySQL and
call them from Python scripts instead of using the apache auth module
directly?

People Store Passwords In Database
i used to store the password in database using md5() function but there is no way to retrieve the

password back.

Now i want to know that -
is it standard and secure way to store password?
is there any other technique to store password so i can retrive it back?

Passwords, Permissions, And Blank Users
I have Apache 2 server, PHP5, and the latest MySQL installed on my system. I'm currently reading PHP and MySQL for Dummies (I told you I was a newbie) and trying to work through a few of the examples.One thing I noticed when I looked at the "users" table in the "mysql" database is a blank user with all privileges on localhost. I deleted that entry, set a password for my "root" account, and created another account with a non-blank password.

The problem I am having is that I am trying to connect to the database as "root" with a blank password, and it's still working. I then tried a blank user, and it worked. I tried a random assortment of letters and numbers, and it worked.

Switched To 4.1, Now Passwords Work Differently - How To Adjust?
I switched from MySQL 3.23 to 4.1. I used Pear DB but now, it doesn't seem to work so I switched to Pear MDB2.

I used MySQL Control Center to add a new system user to the MySQL user table but the password was like an nd5 hash instead of the old 16 character password in version 3.23.

When I try to connect it indicates it can not connect.

Binary Column Types / Hashed Passwords
I'm building an asp.net application using MySQL as the database. I've created a table to store usernames and passwords. During user creation and authentication - i'm presenting the db with a hashed password value.

When a hashed password is stored in a db - how should it appear in the table?

In the MySQL table I'm using to store username and passwords, it appears as
system.byte[]. The data type is defined as varchar(16), binary. The field is not behaving as it should during authentication - as long at I enter a valid username, the system authenticates me despite a correct or incorrect password value.

Can anyone help me to define the appropriate column type? My application works fine w/ MS SQL but fails w/ MySql. In MS SQL the field is described as:[PWD] [binary] (16) NULL

Packed Passwords In Command Line Programs
I think this is a trivial newbie question but can only be answered by a guru.

In command line programs, why should passwords be packed with the parameter name?

For example for mysqlimport one would write the command like that:
mysqlimport -u user -ppass

Why not just
mysqlimport -u user -p pass

?

Repost:Binary Column Types / Hashed Passwords
I'm building an asp.net application using MySQL as the database. I've created a table to store usernames and passwords. During user creation and authentication - i'm presenting the db with a hashed password value.

When a hashed password is stored in a db - how should it appear in the table?

In the MySQL table I'm using to store username and passwords, it appears as
system.byte[]. The data type is defined as varchar(16), binary. The field is not behaving as it should during authentication - as long at I enter a valid username, the system authenticates me despite a correct or incorrect password value.

Can anyone help me to define the appropriate column type? My application works fine w/ MS SQL but fails w/ MySql. In MS SQL the field is described as:[PWD] [binary] (16) NULL

!= Not Working?
SELECT

d.cache_diff,
d.cache_name,
d.cache_type,
d.cache_last_found

FROM geocaches d
LEFT JOIN logvisit ON d.cache_id=logvisit.cache_id
WHERE d.cache_status = 0 && logvisit.log_type != 'Found' && logvisit.log_owner_id = 64

This returns zero rows. Its true that records in logvisit.log_type for this user are only FOUND but I am trying to display cache information for all other caches (in geocaches table) that the user did not log FOUND in logvisit.

So, basically if the user logged against 3 caches and there are 10 caches available to log agains then I want to display only the remaining 7. Is JOIN the way to go on this one?

LIKE '[0-9]%' Not Working
Here is the SQL query:

Code:

SELECT * FROM member WHERE username LIKE '[0-9]%'

Returns 0 rows. I've tried this on 4.1.7-nt as well as 4.0.22-standard; neither work. And yes, there are records in the table that should match that pattern.

Not Working
I have 4.1.10a on my local machine and the following WHERE clause works fine:
Code:

WHERE wk1 IN (select max(wk1) from wkpts where teamid = w.teamid)

But when I upload it to my web site, I get an sytax error at 'select max(wk1) from...'
My web host has MySQL version 4.0.20-standard.
Can someone tell me why it would work locally, but not on my web host's server? Thank you.

LIKE '%$var%' Not Working
I am trying to create an sql statement to return values on either side of the variable.
Code:

$sql = "SELECT * FROM principals LIKE companyName = '%{$_POST['companyName']}%'";

The error was:

Error: 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 'LIKE companyName = '%Mike%'' at line 1

Select As Where Not Working!

SELECT CONCAT(RIGHT(dt,4),'-',MID(dt,4,2),'-',LEFT(dt,2)) AS dateavail FROM atable WHERE dateavail = &#55614;&#57158;-06-01'

(dt is a date in format dd/mm/yyyy - the concat bit works fine)

Microsoft OLE DB Provider for ODBC Drivers error ?e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.19-standard-log]Unknown column 'dateavail' in 'where clause'

Also, if I get it to work, how do I convert the 2 strings to date so I can look for < and > ?

Doing A JOIN Not Working
I have 2 tables, one contains columns that holds the ID to the other table so eg

Table 1 (images)
id | imagepath
--------------
1 dir/hair.png
2 dir/face.png

Table 2 (buildImages)
userid | hair | face
1 1 2

So i'd just like to retrieve a row like

dir/hair.png, dir/face.png

where userid = 1

does that make sense?
Oh, the title of this thread is kind of wrong, shoudl be how do I do this join :P

DATE_SUB() Is Not Working Right For Me.
My `date` column is in date/time format 2007-12-14 19:55:00
When I try to select only the records added in the last 5 hours, I get records from more than 24 hours ago. I know I am EST zone and the server is PST zone, but something else is wrong. This is my WHERE clause:

WHERE `date` > DATE_SUB(CURRENT_DATE, INTERVAL 5 HOUR)

Working With SET Type ...
I have this SET(Ƈ',ƈ',Ɖ',Ɗ') on a column. Suppose that the colunm has the data (1,2,4)
Now i want to select rows that contain either 1 or 2 or 4. What is the fastest way to do this ?

Left(str. Len) Not Working!
BTW: I am very new to PHP & MySQL learning from the site point book ("Build your own database driven website!").

My Question
If i use the left function from the command line, the expected result is returned ok, however when i apply the same query to my web page nothing is returned.

This is my query:
$sql = "SELECT date_added, short_desc, LEFT(long_desc, 10), news_ID, loc_id FROM location Inner Join news ON globalisation = loc_id WHERE loc_id = Ƈ' ORDER BY news_ID ASC";

$getRows = @mysql_query($sql);
if (!$getRows)
{
exit('Error: Loading SQL statements: '.mysql_error());
}
while($listedItems = mysql_fetch_array($getRows))
{
$id = htmlspecialchars($listedItems['news_ID']);
$news_short = htmlspecialchars($listedItems['short_desc']);
$news_long = htmlspecialchars($listedItems['long_desc']);
$date_added = htmlspecialchars($listedItems['date_added']);
echo '<div class="row_container">'
echo '<div class="col_1">' . htmlspecialchars($id) . '</div>'
echo '<div class="col_2">' . htmlspecialchars($news_short) . '</div>'
echo '<div class="col_3">' . htmlspecialchars($news_long) . '</div>'
echo '<div class="col_4">' . htmlspecialchars($date_added) . '</div>'
echo '<div class="col_5"><a href="edit_news.php?id=' . $id .'">Edit<a/></div></div>'
}

DISTINCT() Not Working
Does anyone have any idea why i can't get this to return distinct values?

PHP

SELECT DISTINCT(agi.userid), agi.imageid, agi.title, agi.dateline, agi.catid, agi.thumbname
    FROM a_gallerys_images agi
    WHERE agi.valid = 1 AND agi.open = 1 AND agi.catid = 2
    ORDER BY agi.dateline DESC
    LIMIT 4

MAX(mainid) Isn't Working For Me???
I am wanting to get the last inserted mainid number then insert back into a different row at a higher number. For example if the inserted mainid was 1 the next number should be 2. The code below isn't working for me. It just adds 0 to the mainid column. Can someone please tell me if there is a better way of doing this? Thanks in advance.

Here is my

PHP
require "config.php";
// more code here
$insert1 = "INSERT INTO `menu` (`mainid`)
SELECT max(mainid) + 1
FROM menu";
if (mysql_query ($insert1)) {
$query1 = mysql_query("SELECT LAST_INSERT_ID() as id FROM menu") or die ("Could not query because: ".mysql_error());
$row1 = mysql_fetch_assoc($query1);
$id1 = $row1['id'];
$update1 = "UPDATE menu SET menu = '$menu', pagetitleMenu = 'Menu', pagetitle2 = '$pagetitle', pagetitleSub = 'none', subid = Ɔ', submenu = '$submenuyesno' WHERE mainid = '$id1'";
// more code here
mysql_close();

Working Of Mysql
can any one explain me in details how mysql engine works

Queries Not Working
its been ages since I have worked with MySQL so I am very rusty and am just trying to patch together loads of old code at the moment.

$dbh=mysql_connect ("localhost", "*********", "************") or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("*********");

$result = mysql_query("SELECT * FROM Update
WHERE number=Ƈ');

$row = mysql_fetch_array($result);

echo $row['title'];

mysql_close($dbh);
I keep getting this message: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dcaggorg/public_html/update/index.php on line 18

Working With Dates
After taking a break from this project for a bit I find myself confused about how to pull specific date periods from a database. The timestamps are stored in UNIX format. I'm creating a report builder to pull details about issues that my techs have entered (ISP support center). I can pull reports from calls entered today or yesterday, but getting them from say, mon-fri of last week is throwing me for a loop. Can anyone point me in the right direction? This is what I have thus far:

//echo "DateRange: ".$daterange."<br>";
// begin building our query
$query = "SELECT * FROM issues WHERE ";

// the value passed in date range is how we modify the date lookup in our SQL query
if ( $daterange == "today" ) {
$timestamp = strtotime("today");
$query .= "DATE_FORMAT(CURDATE(), '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "yesterday" ) {
$query .= "DATE_FORMAT(CURDATE() -1, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "thisweek" ) {
$query .= "DATE_FORMAT(CURDATE() -7, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "lastweek" ) {
$query .= "DATE_FORMAT(CURDATE() -14, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "thismonth" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 1 MONTH) >= issue_submitted_date;";
}
else if ( $daterange == "lastmonth" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 2 MONTH) >= issue_submitted_date;";
}
else if ( $daterange == "thisyear" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 1 YEAR) >= issue_submitted_date;";
}
else if ( $daterange == "lastyear" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 2 YEAR) >= issue_submitted_date;";
}

Fulltext Example Not Working
I'm trying fulltext searching for the first time [Ver 12.22 Distrib
4.0.17, for pc-linux (i686)], but I can't figure out why the example
in the mySQL manual is *not* working.

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
Empty set (0.00 sec)

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

Subquery Not Working
CODESELECT parexel_staff.name, parexel_skills.skill, parexel_activity.date
FROM parexel_staff, parexel_skills, parexel_activity
WHERE parexel_staff.name = parexel_activity.name
AND parexel_activity.skill = parexel_skills.skill
AND parexel_skills.skill = 'CGI'
AND CURRENT_DATE > (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
AND parexel_staff.name NOT IN
(
SELECT parexel_staff.name
FROM parexel_staff, parexel_skills, parexel_activity
WHERE parexel_staff.name = parexel_activity.name
AND parexel_activity.skill = parexel_skills.skill
AND parexel_skills.skill = 'CGI'
AND CURRENT_DATE < (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
);

Character Set Not Working
I am using 5.0.24 max on my RedHat 8.0. Having problems inserting persian character set into my database. After searching around and doing what was recommended, I still got no where. I am using a firefox -> PHP -> mysql to enter and read data. Reading data from the command line results in garbage. Here is some useful info:

mysql> show create database mytest;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------+
| mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci */ |

mysql> show create table binfo3;
| binfo3 | CREATE TABLE `binfo3` (
`id` int(11) NOT NULL auto_increment,
`year` varchar(8) collate utf8_persian_ci default NULL,
`make` varchar(15) collate utf8_persian_ci default NULL,
`model` varchar(20) collate utf8_persian_ci default NULL,
`miles` int(11) default NULL,
`price` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100009 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci |

mysql> show variables like 'character_set%';
+--------------------------+--------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-max-5.0.24-linux-i686/share/mysql/charsets/ |

Here is how I connect the DB:
# cat opendb.php
<?php
// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass ) or die ('Error connecting to mysql');
mysql_select_db($dbname);
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER_SET_RESULTS=NULL");
?>

In my browser after entering the data I echo the data and it appears fine. I read it back from the DB and its garbage like:
ID:100008
Year:Û±&
Make:Ø¨Ù†&
Model:Û²Û²Û°
Kilometer:0
Price:0

The & is not suppose to be there. Only 1 char from Year is appearing and 2 chars from the Make appearing. But Model looks fine! Kilometer and price are not accepting any inputs!

SELECT Not Working
SELECT U.*, G.Name AS groupname, C.FirstName, C.LastName,
(Select count * from Account Acc where Acc.User_ID = U.User_ID) as Account_ID
FROM ((Users U LEFT JOIN Groups G ON U.Group_ID = G.Group_ID)
LEFT JOIN Customers C ON U.Customer_ID = C.Customer_ID)
WHERE 1 = 1

The above returns a 1064 Error on SUSE Linux MySQL 4.0.18. But on WinXP MySQL5.0 all runs fine. Can anyone give me advice on how to fix the above to work? If I remove the second select from the query all works fine.

PHP Not Working With MySQL
Just starting out and I have PHP, Mysql, and Apache installed. PHP and MYSQL dont seem to be working together. Everything seems to be running fine and I edited the httpd.conf in order to allow apache to process php requests.

This is what I am doing when I start mysql...
c:/net start mysql
c:/mysql/bin/mysql -u root -p
Then I log in and it accepts the log in.

When I create a sample php program like "Hello", it works.

But when I do a simple sql connect:

<?php
$conn = mysql_connect("localhost","root","tntboy");
echo $conn;
?>

I get nothing! Nothing at all comes up on the screen.
Any ideas? Why is it not connecting to the database?

UPDATE Not Working.
The page that sends information to the following page has a series of text inputs. The name of the input corresponds to the name of the column in the in the mysql table. There's also a hidden input, "id", that supplies the unique id number of the entry.

My script to enter data into the table and to view the table work fine. The update script runs without any errors, but doesn't alter any values. I also tried printing the value of $query and everything appears to be fine. Anyone know why this is happening? Code:

Foreign Key Not Working
I am using mysql server 5 version, and heidisql version 3.0.

i am adding a foreign key to an already existing table

the exact command is

ALTER TABLE mailer_contact_details ADD CONSTRAINT mcd_fk FOREIGN KEY (address_id) REFERENCES addresses(id) on delete set null

there is no error, and i am getting query ok, but the foreign key
functionality is not happening, i am able to enter the value in the
child table which is not present in the parent table, and still no error
message is getting displayed

i even created an index for address_id field, and then again giving
the above command, still foreign key functionality is not working..

where is the problem?