add big text file 14.62 GB to DATABASE

Question

i have written this PHP code:

<?php

$host = "localhost";
$username = "user";
$password = "pass";
$db = "database";

mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_close;


$lines = explode("\n", file_get_contents("textfile.txt"));
foreach($lines as $pass => $password)
{
  $string = trim($password);
$md5hash = md5($string);
$sha1hash = sha1($string);
$res = mysql_query("INSERT INTO `md5` VALUES ('', '$password', '$md5hash', '$sha1hash')") or die(mysql_error());

}
?>

but i still get

Allowed memory size of 734003200 bytes exhausted

Fatal error: Out of memory (allocated 262144) (tried to allocate 15696126973 bytes)

i tried many solutions but that doesnt help! so is there any way to fix this problem by changing or add something to the PHP code ..

thanks

Why are you using an outdated library `mysql` -> surely `mysdli` or `pod` — Ed Heal, Jul 13 '17 at 21:02
***You shouldn't use [SHA1 password hashes](https://konklone.com/post/why-google-is-hurrying-the-web-to-kill-sha-1)*** or ***[MD5 password hashes](http://security.stackexchange.com/questions/19906/is-md5-considered-insecure)*** and you really should use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. Make sure you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Jul 13 '17 at 21:02
It looks like you're setting up a rainbow table, is that the case? — Jay Blanchard, Jul 13 '17 at 21:03
Just checking, because it looks suspiciously like a question which was asked recently and I'd hate to think this is what you're doing this for. Why do you need two, really out of date, hashes for each record? — Jay Blanchard, Jul 13 '17 at 21:07
@JoseLuis Not a rainbow table? Give us a little more credit than that...-_- — whitwhoa, Jul 13 '17 at 21:13

score 5 · Accepted Answer · answered Jul 13 '17 at 20:52

5

You're reading the entire file and then run an explode on that.

Your piece of code tries to allocate 15Gb of memory ;)

You need to read it line-by-line:

$fh = fopen("textfile.txt", "r");
while ($row = fgets($fh)) {
    // $row is the line. in your case $password
    $password = trim($row);
    $string   = trim($password);
    $md5hash  = md5($string);
    $sha1hash = sha1($string);

    $res = mysql_query("INSERT INTO `md5` VALUES ('', '$password', '$md5hash', '$sha1hash')") or die(mysql_error());
}
fclose($fh);

answered Jul 13 '17 at 20:52

Alex Tartan

6,736
10
34
45

thanks but i now get this error >> Incorrect integer value: '' for column 'id' at row 1 – Jose Luis Jul 13 '17 at 20:56
i'm assuming `id` is the first column in your table. That's a different error ;) To fix that, use `VALUES (NULL,` if `id` is an autoincremented integer. If it's not autoincremented, you'll need to supply the value – Alex Tartan Jul 13 '17 at 20:57
that's correct but still the same error >> Incorrect integer value: 'NULL' for column 'id' at row 1 – Jose Luis Jul 13 '17 at 21:04
then use full insert syntax like `INSERT INTO tableName (col1, col2, col3) VALUES ('v1','v2','v3')` By skipping ID, it will be handled by mysql... Buuut.. did you use `'NULL'` or `NULL` (you shouldn't use quotes) – Alex Tartan Jul 13 '17 at 21:06
yeah ! it's fixed now :) thank you but is there a way to pass this >> Duplicate entry 'xxxxxxxxxx' for key 'md5' like if the entry duplicate the code not add them and continue to the next line ? – Jose Luis Jul 13 '17 at 21:10
use `INSERT IGNORE INTO` instead of `INSERT INTO` – Alex Tartan Jul 13 '17 at 21:17
but `INSERT IGNORE INTO` add the duplicated entry to database and i won't add duplicated entry ! – Jose Luis Jul 13 '17 at 21:20
See [mysql insert](https://dev.mysql.com/doc/refman/5.5/en/insert.html) `when INSERT IGNORE is used, the insert operation fails silently` And check this out https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update. In short, it won't insert your duplicate line – Alex Tartan Jul 13 '17 at 21:24
is there a way to sleep 10 mins after number of inserts !? – Jose Luis Jul 14 '17 at 20:26
sure. just add define a `$counter` above the `while` and increment it inside. You can do `if($counter % 1000 == 0) {sleep 600}` (sleep 10 min every 1000 inserts) Change the values to what fits your needs ;) – Alex Tartan Jul 14 '17 at 21:01

add big text file 14.62 GB to DATABASE

1 Answers1