0

I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.

The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.

Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?

Here's what I am trying to do:

LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;

And here's the table:

CREATE TABLE `common_passwords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;

It seems like we should be able to do this rather easily in SQL...

2
  • Without the fields or SET, I get this error: Duplicate entry '123456' for key 'PRIMARY' (123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.
    – DrDamnit
    Sep 24 '18 at 13:53
  • Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements for INTO OUTFILE (Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).
    – DrDamnit
    Sep 24 '18 at 14:36
0

To quote the manual:

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

and

Interpret characters preceded by the escape character \ as escape sequences. For example, \t, \n, and \ signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.

and

If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.

and

... empty ('')

-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html

That is, if you have tabs or backslashes, the data will be mangled as it is read. This may explain the problem you are having.

I guess this may be the fix:

FIELDS ESCAPED BY ''

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.