[RESOLVED] update database from .htpasswd

dabdura

Hello
I don't speak php, I kindly want to ask great coders here for simple script, I know I shouldn't be asking but I wasn't able to get attention from ignorant people on other forums, I've spent yesterday searching for such script with no luck, I dare to ask because i'm sure it's very simple for pros, ok to the subject now.

I have .htpasswd file which is constantly updated by another script with:
user: pass <--pass can be either encrypted or decrypted
I want a simple php code that compares .htpasswd username: password against mysql database username: password and updates mysql database accordingly (removes deleted users, adds new users)

desperately need help
thanks in advance

sneakyimp

Ok let's assume your .htpasswd file lives at /var/www/.htpasswd. You can get its contents like this:

$contents = file_get_contents('/var/www/.htpasswd');

You can then do the rest like this:

$lines = preg_split('/\n/', $contents, -1, PREG_SPLIT_NO_EMPTY);

echo count($lines) . " lines found\n";

$users_seen = array();
foreach($lines as $line) {
  $clean_line = trim($line);
  if (preg_match('/^(.+):(.+)$/', $clean_line, $matches)) {
    $user = trim($matches[1]);
    $pass = trim($matches[2]);

$users_seen[] = $user; // remember all the users we've seen

// query to see if record already exists
$sql = "SELECT * FROM users WHERE user='" . mysql_real_escape_string($user) . "'";
$result = mysql_query($sql)
  or die('query failed:' . mysql_error());
if (mysql_num_rows($result) == 0) {
  // insert the record
  $sql = "INSERT INTO users (user, pass) VALUES ('" . mysql_real_escape_string($user) . "', '" . mysql_real_escape_string($pass) . "')";
  mysql_query($sql)
    or die('insert query failed:' . mysql_error());
}
  } else {
    // bad line?  just ignore it or error here
  }
}

// finally, delete all user records we didn't see in the file
foreach($users_seen as $key => $user) {
  $users_seen[$key] = "'" . mysql_real_escape_string($user) . "'"; // escape and quote the usernames
}
$sql = "DELETE FROM users WHERE user NOT IN (" . implode(',', $users_seen) . ")";
msyql_query($sql)
  or die('delete query failed');

EDIT: fixed the regex pattern.

dabdura

Thank you so much, I thought I would never achieve this, although I'm a complete noob on php, you can't beat the logic so I followed the logic and modified the code.
I was getting this error:
1 lines found
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'removed'@'localhost' (using password: NO) in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 18

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 18

Warning: mysql_query() [function.mysql-query]: Access denied for user 'removed'@'localhost' (using password: NO) in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 19

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 19
query failed:Access denied for user 'removed'@'localhost' (using password: NO)

I figured it couldn't login to my DB because dbname dbpass db was never defined so I modified th code like this:

config.php:

<?php 
    $host="localhost"; 
    $dbUserName="myDBusername"; 
    $dbPassword="myDBpassword"; 
    $dbName="myDBname"; 

?>

<?php
include("config.php");
$contents = file_get_contents('my path to /.htpasswd');
$lines = preg_split('/\n/', $contents, -1, PREG_SPLIT_NO_EMPTY); 

echo count($lines) . " lines found\n"; 

$users_seen = array(); 
foreach($lines as $line) { 
  $clean_line = trim($line); 
  if (preg_match('/^(.+):(.+)$/', $clean_line, $matches)) { 
    $user = trim($matches[1]); 
    $pass = trim($matches[2]); 

$users_seen[] = $user; // remember all the users we've seen 

// query to see if record already exists "i added below three lines and was surprised i did it correctly :)"
mysql_connect("localhost",$dbUserName,$dbPassword) or die(mysql_error());
mysql_select_db($dbName) or die(mysql_error());
mysql_query($sql);

$sql = "SELECT * FROM myDBtable WHERE myUserColumn='" . mysql_real_escape_string($user) . "'"; 
$result = mysql_query($sql) 
  or die('query failed:' . mysql_error()); 
if (mysql_num_rows($result) == 0) { 
  // insert the record 
  $sql = "INSERT INTO myDBtable (myUserColumn, myPasswordColumn) VALUES ('" . mysql_real_escape_string($user) . "', '" . mysql_real_escape_string($pass) . "')"; 
  mysql_query($sql) 
    or die('insert query failed:' . mysql_error()); 
} 
  } else { 
    // bad line?  just ignore it or error here 
  } 
} 

// finally, delete all user records we didn't see in the file 
foreach($users_seen as $key => $user) { 
  $users_seen[$key] = "'" . mysql_real_escape_string($user) . "'"; // escape and quote the usernames 
} 
$sql = "DELETE FROM myDBtable WHERE myUserColumn NOT IN (" . implode(',', $users_seen) . ")"; 
msyql_query($sql) 
  or die('delete query failed'); 
?>

at this point new users are added to DB but removed users not deleted and i get this error:
2 lines found
Fatal error: Call to undefined function msyql_query() in /home/flashygf/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 41

dabdura

Thank you so much, I thought I would never achieve this.
although I'm a complete noob on php, you can't beat the logic so I followed the logic and modified the code.
I was getting this error:

1 lines found
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'removed'@'localhost' (using password: NO) in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 18

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 18

Warning: mysql_query() [function.mysql-query]: Access denied for user 'removed'@'localhost' (using password: NO) in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 19

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 19
query failed:Access denied for user 'removed'@'localhost' (using password: NO)

I figured it couldn't login to my DB because dbname dbpass db was never defined so I modified th code like this:

config.php:

<?php 
    $host="localhost"; 
    $dbUserName="myDBusername"; 
    $dbPassword="myDBpassword"; 
    $dbName="myDBname"; 

?>

<?php
include("config.php");
$contents = file_get_contents('my path to /.htpasswd');
$lines = preg_split('/\n/', $contents, -1, PREG_SPLIT_NO_EMPTY); 

echo count($lines) . " lines found\n"; 

$users_seen = array(); 
foreach($lines as $line) { 
  $clean_line = trim($line); 
  if (preg_match('/^(.+):(.+)$/', $clean_line, $matches)) { 
    $user = trim($matches[1]); 
    $pass = trim($matches[2]); 

$users_seen[] = $user; // remember all the users we've seen 

// query to see if record already exists "i added below three lines and was surprised i did it correctly :)"
mysql_connect("localhost",$dbUserName,$dbPassword) or die(mysql_error());
mysql_select_db($dbName) or die(mysql_error());
mysql_query($sql);

$sql = "SELECT * FROM myDBtable WHERE myUserColumn='" . mysql_real_escape_string($user) . "'"; 
$result = mysql_query($sql) 
  or die('query failed:' . mysql_error()); 
if (mysql_num_rows($result) == 0) { 
  // insert the record 
  $sql = "INSERT INTO myDBtable (myUserColumn, myPasswordColumn) VALUES ('" . mysql_real_escape_string($user) . "', '" . mysql_real_escape_string($pass) . "')"; 
  mysql_query($sql) 
    or die('insert query failed:' . mysql_error()); 
} 
  } else { 
    // bad line?  just ignore it or error here 
  } 
} 

// finally, delete all user records we didn't see in the file 
foreach($users_seen as $key => $user) { 
  $users_seen[$key] = "'" . mysql_real_escape_string($user) . "'"; // escape and quote the usernames 
} 
$sql = "DELETE FROM myDBtable WHERE myUserColumn NOT IN (" . implode(',', $users_seen) . ")"; 
msyql_query($sql) 
  or die('delete query failed'); 
?>

at this point new users are added to DB but removed users not deleted and i get this error:

2 lines found
Fatal error: Call to undefined function msyql_query() in /home/removed/public_html/au2GzeEf7Qxbv8YVcn5qFUBX6jJsSDTw/NewFolder/sneakyimp.php on line 41

dabdura

sorry for double posting

just noticed, line 41 was mistyped, i corrected: msyql to mysql 🙂
I also removed mysql_query($sql) from line 20 which i added earlier

works like a charm, i'm testing it right now, i'll ask if anything, if i may 🙂
mr.sneakyimp thanks, grazie, danke, merci, obrigado, gracias, спасибо, 谢谢, you're the man, you're the greatest

sneakyimp

glad you have it working. way to stick with it.

deumatasha

Hi dabdura,

I have been following some of your post in other forums and now I found where you acutely solved you problem.

Well i am having the same problem, I and going to trying your example. What I would like to know, since I know very little about php. Where would I need to place this script to run it, can it be place in my website folder and run from my website example: www.mysite.com/updatedb.

Thank you in advance.