MYSQL :: Password() Function

PASSWORD() Function
I'm writing a login.php script for my web site and am encountering some problems matching the password of the user logging in. Here's some of my code where I believe the problem to be:

$query = "SELECT user_id, first_name FROM users WHERE username='$u' AND password=PASSWORD('$p')";
echo($query);
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
if ($row) {

// Start the session, register the values & redirect.
session_start();
$_SESSION['first_name'] = $row[1];
$_SESSION['user_id'] = $row[0];
header ("Location: [url]http://"[/url]; . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/loggedin.php");
exit();

} else {
$message = '<p>The username and password entered do not match those on file.</p>';
}
mysql_close();
} else {
$message .= '<p>Please try again.</p>';
}
}

I keep getting my error message "The username and password entered do not match those on file" listed above in the conditional. The server doing my hosting is running MySQL 4.1.14 - standard. The test login script that is getting passed prints out as follows:

SELECT user_id, first_name FROM users WHERE username='test' AND password=PASSWORD('test1')
Am I doing something wrong with the PASSWORD() function?

Problem Logging In Using Mysql Password Function
I have a test server on a windows box with php 4.3.10 and mysql 4.1.10-nt. I built a login app and It works well using the mysql password('') function. Unfortunately I ported the app to a freebsd box running php 4.3.10 and mysql 4.1.12. The passwords come out differently and I don't understand why.

For instance, the password test looks like "378b243e220ca493" on the windows box and the freebsd database entry look like "*94BDCEBE19083CE". As you can see, very different. And this causes a lockout from logging in.

Instead of being routed to a "safe" login page, the error for a wrong username/password combo shows up.

Mysql Appears To Have Changed The 'password' Function Hashing
I've been using Mysql for about 6 months but in fairly basic fashion. For a cms I have built, I have a users table, with userName and password fields. An admin can create new users, and the password is hashed using the PASSWORD() function.
SQL: "INSERT INTO users VALUES ('', '$userName', PASSWORD('$userPwd'))

Straight out of the book basically. Now the problem is that all this has been functioning fine on a couple of sites on external servers, plus a couple of local machines.

Until today, on my main dev machine - yesterday a user could log in, today they couldn't. As far as I can tell, nothing has changed. But when I look at the hashes in the database table, they are 41 chrs, and when I add a new user, the hash is 16 characters.

I know that at some stage the hash length was increased between mysql versions, but I have not updated the install (I run XAMPP for Windows 1.4.14, which appears to install php 5.0.4 and mysql 4.1.12)

Is there any other reason that the hash length could change to 16? I'd like to know why this happened so that I'm prepared if it happens on the server (where it is not so easy to hack in and add new users that can log in again)

Warning: Mysql_connect() [function.mysql-connect]: Access Denied For User 'debscrof'@'localhost' (using Password: NO)
Have MySQL on a VPS running CPanel
MySQL v4.1.22-standard

Can access MySql fine through CPanel, phpMyAdmin etc all work fine, creating databases, deleting databases, adding users etc

However, when I try to use MySql from a website, it fails

I created a user "test1" (which then got the username "debscrof_test1" from CPanel), a password for the user "test2", and then added the user to a new database "testdb" with all priviliges

Then a simple script to connect to the database (as I am trying to locate the source of the errors), as per below:

<?php
$dbhost = "localhost";
$dbuser = "debscrof_test1";
$dbpass = "test2";
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'debscrof_testdb';
mysql_select_db($dbname);
?>

Have tried with " and ' around the values, with 127.0.0.1 as the host... still with the same error message:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'debscrof'@'localhost' (using password: NO) in /home/debscrof/public_html/testdb.php on line 6

Error connecting to mysql

...any suggestions why I cannot connect? (and why it says password: NO)!

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over Â£300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

Random Password Vs. User Created Password For Site Login
Are there any security issues or other concerns that make one preferable? The client is pushing for user created passwords.

I'm mapping out the basic functionality and front-end for a MySQL/PHP back-end that will be completed by a third party. I'm a novice to MySQL but familiar with PHP and their interaction. Mainly looking for anything to support one method over the other.

Mysql Password() & Unix Crypt Password...
I am creating a user administration system where system administrator
can activate services for a user, like webspace, a mail account or a
subdomain.

I now want to create a function that creates mysql databases and grant
the right privileges to a user. But the problem is that mysql wants to
have the plaintext password for the user in the "grant ... identified by
'pwd'" field, or in a manual query to update the password in the
mysql.user table with PASSWORD('pwd').

I have another database which holds the passwords for the users, these
password are stored the moment a user signs up and are used to activate
services, for example ssh access to the machine. The stored passwords
are encrypted using crypt(). So for adding a ssh service to a user, I
simply do echo "$user:$pwdhash" | chpasswd -e.

I have chosen crypt because some programs (like proftpd) don't take md5
sums.

The problems is that I don't have the plaintext password. So I cannot
add a mysql db.

So I need to create a user in mysql with a crypt password.
I tried to just "grant all privileges ... identified by 'nothing'"
and then afterwards update the mysql.user table and putt the crypt
password in there but it doesn't work like that.

Does anyone have any idea on how I could fix this, how can I create a
user in mysql without having his plaintext password and only having a
crypt hash of it.

I thought about not only storing a crypt hash, but also an md5 hash and
a mysql PASSWORD() hash.. but I think that's an ugly solution.

I Can Connect With Db Password Or Blank Password
I have a password setup for root (% and localhost) in Mysql 5.0.26, I can connect ok no problem. I can also connect with blank password! I know it is checking the password cuz it fails if it's wrong.

I'm sure I have only one account as well (tried to delete, recreate, etc... no change)
select * from user where user='root';
| localhost | root | *F3AD8B3C44772C17F66767F29D948D9C255BD824 | Y ...
| % | root | *F3AD8B3C44772C17F66767F29D948D9C255BD824 | Y ...

Is there an option to accept blank password!? I have this problem only for root user; Other accounts are fine.

I Changed Root Password But I Can Not Login With Changed Password
I changed the password of root with
update user set password=password('newpassword')

Then I exited. But when I want to login again , it cannot authenticate the root
I should tell you that a hacker had changed my root password before. Because it was not encrypted before.

Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?

Self Run Function/..
I need to delete some records from my table every 5 minutes. I thought that would be better if done via a trigger, function, etc. But I don't know how to fire a trigger every 5minutes or is there any other way to do that?

Using The AVG Function
I am trying to use this query:

SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' )

to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?

Looking For A Particular Function
I have a Column which consists of only DATE's and an ID code. I want to enumerate all the DATE's of a certain ID to get a grand total of a ID. Does such a function exist?

Avg Function
Why doesn't this work:

SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value

I'm trying to select the avg_value of 'r' where the avg_value = '100'.

Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance.

select *
from accounts
group by date(dateCol)

This always gives me the first row of the grouped column dateCol.

NOW() Function
We are developing a tool here that uses a php script to insert data into a MySQL 5.0.41 database running with the INNODB engine. While tracking a bug we noticed a strange effect.

Each insert query contains a call to the NOW() function to store the create data of the record into a field automatically. Therefore we can expect in my opinion that the timestamps of the records are increasing simultaniously with the autoincrement key of the table. This actually is not the case. On the contrary the timestamps are jumping up and down with large differences. For example we have the record with ID1 that got the timestamp 14 minutes 2 seconds where ID2 got 15 minutes 15 seconds and ID3 14 minutes 9 seconds. How could this be? Does it mean that the server does not execute the statements in the order he receives them? Is it possible that it took more than a minute to execute a simple insert statement?

Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this

select name, priority, parent from table1
select max(priority) from table1 group by parent

are the two select statements that I wish to combine...is it possible..?

Function In C
I was trying to figure out how would you write a function in MySQL using C. I saw that theres a CREATE FUNCTION syntax but am confused on how to compile and write a C function.
For example if I wanted to write a function that added an extra period after every period and called it ADD_PERIOD() how would I do that.

Example:

x = "joe runs far."
SQLSTRING = "SELECT * from Story where Sentence = '"+ADD_PERIOD(x)+"'";

this would return. joe runs far

SQL IN() Function
so i have a query such as
SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)

2 Queries In 1 Function?
Is it possible to query the database twice in the same function?

If so is it possible to use results form the first query IN the second?

Count () Function
Cant anyone tell me why this very basic query won't process.
This is to give a breakdown of Mem.heard 1-12 numbers ("where did you hear about us survey") for ea of the MOrig two user types

EX:
MOrig | heardof | count_heardof

UserA 1 5 (times)
UserA 2 6 (times)
UserB 1 3 (times)
UserB 2 8 (times)

SELECT MOrig, heardof, count ( heardof ) as count_heardof
FROM Mem
GROUP BY MOrig, heardof
ORDER BY MOrig

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

SUBSTR() Function
SELECT SUBSTR(message, 0, 20) FROM posts

My goal is to start at the first characters on the far left, and then return 20 chars only. This function for some crazy reason seems to start at the end of the string though, which is stupid, because how do I know where the string starts?

Hidden_field Function
Does mysql 4.0.24 standard have a hidden_field function? I need to maintain state from one form to another and hidden_field is used in a script I have using mysql 3.22. If 4.0.24 doesn't have hidden_field function, what can I use with mysql 4.0.24, which is used on my server?

Date Function
I have a time stamp field in my orders table and I want to form a select statement where it returns all orders with an order time of two weeks ago from the current time or later so I can keep better track of unpaid orders.

Here are the fields for the orders table
orderID
order_time
order_status
etc...

SELECT orderID FROM orders WHERE ???? AND order_status=2 ORDER BY order_time ASC

Thanks for any help. The date functions are a little confusing to me from the manual.

Function ROW_INDEX
I couldn't find a function that numbers the resultset of a query. First row should be 1, second row 2 etc.

I searched for rownum, rowcount, rowindex etc with and without underscore's.

Mysql_query Function
I making a loop of the returned result set from mysql_query, it is very important to know if an update occur during the loop to the table affect the result set of the loop or not ?

I need to loop blindly without see any updates or inserts occur during the loop....

Counting Function
I have a table with a list of names and some are duplicate and I would like to count them?? It's like this:

Drew
Simon
Alli
Tim
Beth
Alli
Simon
Drew
Simon
Simon
Alli

Then to export:
Drew - 2
Simon - 4
Alli - 3
Tim - 1
Beth - 1

How could I go about asking this query?

SELECT COUNT(name) FROM table

??

Random Function?
I would like to know if there is an existing random function I can use to generate a random number so I can concatenate it to a string?

AES_ENCRYPT Function
Essentially I am playing around with the aes_encrypt fucntion to securly store some data in the db. The problem I am having is that I get the following error : Data truncated for column 'foo' at row 1 and the table creating was done with:

CREATE TABLE `foobar` (
`foo` varchar(10000) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

with a varchar(10000) and doing and AES_ENCRYPT('foo','bar') one would think that it would work. But I suspect that I am doing something incorrectly.

Using Unix_timestamp Function
Can anyone see anything wrong with the way I am using the MySQL unix_timestamp function in this snippet from a query?

$importdata="INSERT INTO import
(date, gluc, humilog, regular, lantus, carbs)
VALUES (unix_timestamp('2004-08-01 05:04:00'),152,02.0,00.0,00.0,00.0)

The table column where I am inserting this was created as:

"date timestamp not null"

I am getting an incorrect timestamp value (20001228122240)that converts to "Mon, 18 Jan 2038 20:14:07 -0700" in PHP. As far as I can see, my usage of unix_timestamp is the same as that in the MySQL manual.

RAND Function
Basically, I have a stored function that is designed to deliver a random 3 digit code. But I need it to be of the format #~#~# (ie, 3~0~7, 5~1~5, etc) So I set up the function to generate 3 random digits and then concatenate them together, using the following lines inside the function:

SET v1 = FLOOR(RAND() * 10);
SET v2 = FLOOR(RAND() * 10);
SET v3 = FLOOR(RAND() * 10);
SET vCode = CONCAT_WS('~', v1, v2, v3);

The function works fine, and gives me back my 3 digit code in the exact format that I want.

However, after testing it out, I've discovered that it isn't entirely random. There are certain combinations that I never end up getting returned from the function. There are basically 1000 different possible combinations, but I'm only ever receiving 800 combinations back. I've loop tested the function to generate literally millions and millions of different codes, and there are certain codes that I just never see. Code:

Function In Index?
I have a table that has a datetime field called (confusingly) 'date', but a lot of my queries do DATE(`date`).

I decided to add an index on DATE(`date`), but it doesn't seem to make much difference in speed. Is it valid to put an index on a function of a field?

Function Transaction
I'm working on a little framework based on SQL, i'm fighting with session becouse i'm need to check if the user is login or not and then if can see the resource or not, it's can be change for each resource. Code:

Stored Function
I managed to make a stored function 'IsEmpty' but when I use the function in a query like:
select * from tblinre where isempty('') ;

it will result in FALSE. when I use use isempty(null) it will result in TRUE.

my stored function

DELIMITER $$

DROP FUNCTION IF EXISTS `bridge`.`IsEmpty` $$
CREATE FUNCTION `IsEmpty`(p VARCHAR(1)) RETURNS tinyint(1)
DETERMINISTIC
BEGIN
RETURN IF(TRIM(p)='' OR p IS NULL,TRUE,FALSE);
END $$

DELIMITER ;

CASE Function
The following excerpt from an SQL Script outputs a table containing a column entitled "TypTitle" containing rows of PASS and FAIL.

Is there a way to change the output column name from "TypTitle" to "RESULT" without changing the database? Code:

DateDiff() Function
I am trying to use the MSSQL function DateDiff('m', date1, date2) in MySQL but mysql do not support the type 'm' or 'd' or 'yyyy'. I do not want to receive the datediff value as a SELECT field e.g.

"SELECT datediff('m',dateofbirth,'2005/06/23') from Dependant WHERE TranID = 3068"

CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35

The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

Mysql Function
i am hosting my website on a hosting website server. I made Mysql database there. I noticed one thing when i insert data in tables they have a colum called "function" with drop down box where they have values like ASCII,CHAR,SOUNDEX, UCASE, LCASE, COUNT,AVG any many other.

I don't pick any of them. But I am just wondering if I need to pick either ASCII or char?

Function Abs Doesn't Exist
I nstalled a mysql 5.0 Database on one of my servers but I receive the following error when I want to use a function from MySQL.

create view myView AS
select DATA,TIMED from myTable where ( abs (1138612032902 - myTable.TIMED)*11) >10 ;

And I receive :
ERROR 1305 (42000): FUNCTION MYDB.abs does not exist

I want to know if somebody has faced this error and I appreciate any solutions for this problem.

TRIM Function
My column from a table in sql has value "NEW" and sometimes "NEW ".....thats with 2 spaces behind it...

How do i get rid of it in my sql or asp....someone suggested TRIM??

mysql="SELECT * FROM Test WHERE number='10'"
Set rs= Con.Execute( mySQL )

if rs("status")="NEW" then
...
else
...
end if

Substring Function
I am trying to figure out a SQL statement. I have created a table called employees with fname, lname, and email fields. I have several records in this table. All records have values in the email field, but not the fname or lname fields.

I would like to create a sql statement to parse the email field (which consists of the first and last names of the employee in this format: first.last@company.com).

I know I am way off, but this is what I have so far:
UPDATE `employees` SET `fname`='what goes here?'
UPDATE `employees` SET `lname`='what goes here?'

Being new to SQL, what function do I use to search for a particular substring of the email field, such as 'first' for the fname, and 'last' for the lname? I am aware of the left() function and mid() function, but the strings vary in length.

Replace Function
I am not an experienced user of MySQL and we are trying to produce a basic report but we are having trouble getting the replace function to work. We have a unique indentifer (contract id) from a finance package but need to delete the suffix "CS" from the contract id : we have used the following code:

REPLACE(ContractId, 'CS', ' ')

However, when we run our Crystal report the contractid is limited to a string of 6 yet we need to ensure that at least 10 char are returned.

Count Function
I have a table with 40 rows. After I connect to the server and select a database I try to store the number of rows in a certain variable using php.

I'm using....

$count = mysql_query("SELECT count(*) FROM League1);
echo $count;

...when I do this it displays "resourceID#2resourceID#4"

For some reason this made me think I was getting an array instead of a straight up number so I added this line before echo $count;

$count = count($count);

....and that just displayed "11" no matter how many rows there were in the table.

After all of this I am getting the feeling that I am doing something totally wrong but everywhere I look to find out what I did wrong I get either an answer that is specific to another problem or something way too general.

Date Function
How does DATEADD(d,-1,DATEDIFF(d,0,GETDATE())) represent yesterday's date...

My explanation which does not fit here is that datediff will do today-0 and will return 8 as today's date is 8th. Then for DateAdd don't we need a date to do the calculation...how does it do 8-1=7 which is yesterday's date?

YEARWEEK Function
I have just discovered the YEARWEEK date function and very useful it is too! However, having changed a date into a YEARWEEK I now need to convert it back again - I want the date of the first day in that week. Is there an easy way to do this? I'm thinking something like:

my $year_week = 'select YEARWEEK($date)';

my $first_day_of_week = 'select FROM_YEARWEEK($year_week)';

Using Distinct In Sum Function
i keep getting 1064 :

SELECT
userID, COUNT ( DISTINCT failreasonid )
FROM
MyTable
WHERE
number like '923%' and
date between 2005-08-10 and 2005-08-21
GROUP BY
userID

Calling Db Function
how do i call a db function from with in a db function which returns a result. can some one pls help me with the syntax, when i call a function from with in a function it says funtion does not allow to return a result set where as it returns only one values.

Summary Function
I have only one value in a row with multiple rows with different single values in that column. I would like to get an average of the same value in that column from multiple rows. How can I do that?

e.g column name sbp with single value in 45 rows. If I ask for any summary function (eg avg(sbp), min(sbp) man(sbp)) I get multiple values, one for each row, and my summary values are all the same (of course - because there is only one value per row)!

Is there a way (without writing a php or some other program) to get an average of the multiple values in multiple rows under the same column?