Summary of experience in establishing a social engineering database

Summary of experience in establishing a social engineering database

The "Social Engineering Database" is a structured database that uses social engineering for penetration tests to accumulate data from various aspects.

Environment Introduction

① Host: Fedora (English) (server), win8 (Chinese)
② Fedora install apache, Mysql Workbench, and Language Pack (selected when Fedora is installed ).
③ Database: Mysql is installed in Fedora. The Database/table encoding is UTF8. Modify datadir = a separate partition in my. cnf. tmpdir = the space is larger than 16 GB ).
④ Win8: notepad ++ and Navicat are installed.

The MySql configuration is as follows:

/etc/my.cnf

[mysqld]skip-name-resolve#datadir=/var/lib/mysqldatadir=/home/data/mysql/tmpdir=/MysqlTemp/#socket=/var/lib/mysql/mysql.socksocket=/home/data/mysql/mysql.sockuser=mysqldefault-character-set = utf8max_allowed_packet = 200Mnet_buffer_length = 65536wait_timeout = 2880000interactive_timeout = 2880000symbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysql]socket=/home/data/mysql/mysql.sock[mysqldump]socket=/home/data/mysql/mysql.sock[mysqladmin]socket=/home/data/mysql/mysql.sock

Modify datadir, tmpdir, and socket.

Some people may still use txtSearch. I think it's really slow...

 

Preparation

① Txt volume not more than m will be first converted into UTF-8 encoding format (8g memory graphics can use Notepad ++)
② Check whether the txt file contains "(double quotation marks)" (single quotation marks), which may lead to unexpected termination. (There are other reasons to mention later)
③ If multiple txt files with the encoding format are split into small and small parts, the encoding problem occurs when cat *> 1.txt in linux is used (that is why the first one is executed first ), in fact, windows can use type *> 1.txt. (Note: In windows, the type * source file and the target file must be in different directories. In windows, the command tool is indeed a bit scum. Cough ~)
④ Check whether there is an index in the SQL format file during table creation. If you delete the file, import the file and create it again, and the speed will be improved a lot.

Data Import

① SQL file: After the key 'user' (user) is deleted, mysql executes source xxx. for SQL import, you don't need to consider encoding. After the database is imported into the database, you can directly use workbench or nevicat to edit the database attribute and convert it to UTF8 encoding, and then add the index.

② Txt file (use tab interval and press enter to wrap the line), select into outfile txt/bcp can use mysql Command load data infile a.txt into table test. a; (fields terminated by '\ t' enclosed by ''' escaped by' \ 'BY default, and lines terminated by' \ n' starting by ''BY default '')

 

③ Txt file (non-tab regular delimiter), which can be imported using Navicat.

 

④ Txt file (irregular interval), use py to process it into ordered text, and then use Navicat to import it.

 

Is an irregular space + tab, write the code for processing.

 

When you encounter several exceptions, take a look at the proportion.

 

Except t:

Count = count + 1

 

To see the processed results.

 

 

⑤ Convert mssql to mysql

(I) MySQL-Migration-Toolkit
(Ii) Mssql to Access then access to MySQL
(3) BCP then Load data local infile txt into table xx;

Run Windows (mssql) cmd

Bcp "select * from dbname.dbo.info" queryout info.txt-c-x-S127.0.0.1-Usa-P123.456 or bcp dbname.dbo.info out info.txt-c-x-S127.0.0.1-Usa-P123.456

Fedora (MySQL) Execution

mysql -u root -puse test;Load data local infile "/home/info.txt" into table xx;

⑥ EmpireBak empire backup king

Deploy EmpireBak in apache and Set directory permissions. Copy the backup data to the bdata directory. Log on to the main interface, set parameters, connect to the database, and select recover data to select the database to be restored.

Optimization

① Data Simplification

Delete from 'xxxx' where mail not like "% @ %"; delete records that do not conform to the mail format delete FROM 'xxxx' where mail is null and length (username) <4; delete a record with an empty email and the username length less than 4 FROM 'xxxx' where length (password) <4; delete a record whose password length is less than 4 from 'xxxx' where mail is null and password = username ;......

② Data deduplication

Method 1

delete from `xxx` where id in (select * from (SELECT  min(id) FROM `xxx` group by mail having count(mail)>1) as a);

This is the most common statement mentioned in Baidu's article. Million data is deleted after three hours of data processing.

Method 2

create table tmp_xxx select min(id) as id,mail,password from xxx group by mail,password;

The same million data deduplication takes less than two minutes. (160 w spend 56 s)

Note: The most common problem with this command is that the temporary space is insufficient. If tmp is a small partition space, you can modify tmpdir =/otherdir Of my. cnf. Ensure that the otherdir space is sufficient (> 16 GB ). Modify the otherdir permission. mysql can access and modify the permission. Restart mysql.

 

③ Query Optimization

(I) exact or fuzzy queries, such as mobile phone numbers, and fuzzy queries such as user names and mailboxes. = Is more efficient than like.
(Ii) Use 'string % 'in fuzzy search instead of' % string % '.
(3) Try to use single quotes in SQL statements.
(4) If the ID is the same as the number of rows, select * from xxx where id = 888; you can use select * from xxx limit 887,1; instead (used when no index is available ).

④ Add an index

Add indexes to common query fields. The fuzzy class uses the B-tree storage type, and the precise class uses the HASH Storage type.
We recommend that you use Navicat to select a table to open the table information. On the DDL tab, you can see the SQL statements of the table clearly. If there is any index, right-click the Database Name and select its console function, to quickly add an index.

ALTER TABLE `XXX`ADD INDEX `inx_username` (`username`) ,ADD INDEX `inx_mail` (`mail`) ;

⑤ Use MyISAM for table Type

MyISAM tables emphasize performance. The execution speed is faster than that of InnoDB tables, but transactions are not supported. In addition, the indexes and data of MyISAM are separated, and the indexes are compressed, so the memory usage increases a lot.

Question

① Questions about cross-platform recovery using empire backup king

(I) Open the local page and the page is blank.

Empire backup uses a php format that is not fully standard ( In short mark format. So the solution is to configure php. ini and start the short tag format.

short_open_tag = On

(Ii) When you recover data, click Start recovery, and the system prompts that the parameter is incorrect.
Make sure that the name of the database to be restored is the same as the backup name (you can view it in config. php under the backup file directory). Make sure that the backup php source file has read and write permissions or chgrp apache *-R.

② PHP Problems
(I) Fatal error: Allowed memory size of 134217728 bytes

The maximum independent memory usage of a single thread exceeds 128 MB. Modify/etc/php. ini

memory_limit=512M

(Ii) Time Zone Error

Modify/etc/php. ini

date.timezone = Asia/Chongqing

(3) modify the default mysql location

Modify/etc/php. ini

mysql.default_socket = /home/data/mysql/mysql.sock

(Iv) Illegal mix of collations (gbk_chinese_ci, IMPLICIT) and (latin1_general_ci, IMPLICIT)

The character encoding is inconsistent between several queried tables and cannot be displayed on the current page. Convert to UTF8.

③ SQL encryption, hex

 

 

④ MySQL Problems

(I) [Err] Got a packet bigger then 'max _ allowed_packet 'bytes
Baidu, max_allowed_packet = 500 M, but the error persists. (Those that only know the reprinted goods)

Let's see the error message. Is it a double quotation mark? See article 2nd.

 

(Ii) MySQL server has gone away

Baidu: 1. It may be that the SQL statement sent is too long and exceeds the size of max_allowed_packet. If so, you only need to modify my. cnf and increase the value of max_allowed_packet. (This is another set, I # $ % &#*~)

2. The long-time execution of MYSQL statements by the application (PHP) exceeds the wait-timeout time of mysql.

Modify/etc/php. ini

wait_timeout=2880000 interactive_timeout = 2880000

But the problem persists. I encountered several times in the actual operation process, most of which are the two cases.

Α when you use MySQL Workbench to add an index to entries of data.

When removing duplicates using Navicat.

Use Terminal.

 

Ma no longer has to worry about leaving MySql.

(3) If the SQL file imports indexes, check whether the index is valid.

 

Suggestions

① Based on the poor disclosure of various database layers, apart from the enhancement of cryptographic algorithms, our own passwords must be both complex and confusing. For example, i1o0' ", |/\ r \ n;
② The scalability of databases and web scripts should also be considered. A new table should be created separately for tables of different sources.
③ The query time of each table on the web page can help you locate it accurately.

 

④ For Data Optimization, we recommend that you retain the regip address when receiving the database, and use the following statement to optimize the database.

select regip,count(*) group by regip order by count(*) desc;

You can understand the role of this statement. Thank you. Then you can understand how to do it.

Instance

Instance 1

Take the gmail-5000000.txt file generated by a forum outside China in-as an example.

1. Check character UTF-8, check single double quotation marks, both single quotation marks, double quotation marks and Tilde. (It seems that some people outside China have a strong awareness of passwords)
2. The Delimiter is ":", Statistics ":" tens of thousands more than the number of rows. Obviously, some of the passwords contain ":". (It cannot be simply solved with Navicat)
3.modify the first colon (\ t) in gmail.txt for pyscript replace.

 

 

Well, there can be no less than one.

Instance 2

Take the image as an Example

1. Use "--" as the delimiter to count the number. (Can someone use this password ?)
2. navicat cannot use two or more separators.
3. Create a table first, and then use the load data local infile method. (Do not forget to convert it to UTF8. Of course, you can import it to the database)

 

Load data local infile `/home/xx/Desktop/b14.txt' into table `tn` fields terminated by '----';

 

What! With so many warnings, let's see what it is.

 

So many junk mailboxes...

One set of Data Optimization:

delete from xxx where mail like 'aaaaaa%' and password like '111111%';delete from xxx where id in(619665,618512,615362,606705,591547,571497,514328,509353,509349,509345,509342,494415,374357,346237,281087,255543,236556,227425,226934,22391,190644,161981,160870,132437,112240,86570,14403,9667);   ......

Make up your brain when you see the situation.