iptables: Blocking attacking IP addresses

Excellent article on using iptables to block an attacking IP address.

http://www.cyberciti.biz/faq/linux-iptables-drop/

I needed to block a known russian SSH/SFTP hacker.

Code:   

iptables -A INPUT -s 91.205.189.27/255.255.255.255 -j DROP

   


Even better for automated blocking of attackers is denyhosts. DenyHosts is a script intended to be run by Linux system administrators to help thwart SSH server attacks (also known as dictionary based attacks and brute force attacks).

http://www.cyberciti.biz/faq/block-ssh-attacks-with-denyhosts/

Copy table between two Databases

Code:   

CREATE TABLE DB2.new_copy LIKE DB1.master_copy;
INSERT INTO DB2.new_copy SELECT * FROM DB1.master_copy;

The first line creates the table using the same engine as the existing table and creates the primary keys and/or indexes.
The second line copies the data across.

If the original table has many keys then it may help to speed the INSERT if you turn off the keys using the following before the INSERT:

Code:   

ALTER TABLE new_table_name DISABLE KEYS;

   
And then after the INSERT:

Code:   

ALTER TABLE new_table_name ENABLE KEYS;

   
These two statements are not supported by all MySQL Engine types though. InnoDB being one of them in MySQL ver 5.x

Linux: 20 Iptables Examples For New SysAdmins

Very good article on some essential iptables firewall rules.
http://www.cyberciti.biz/tips/linux-iptables-examples.html

Select data from table WHERE not matched in another table

The following SQL example allow you to select data, excluding data that is in another table.

You can off course include more selection criteria after the WHERE clause.

Code:   

--  Select data from table WHERE not matched in another table
SELECT data_from_table.*
FROM data_from_table
LEFT JOIN exclude_table
ON data_from_table.email = exclude_table.email
WHERE exclude_table.email IS NULL;

So, data will be selected where the email address does NOT match an email address in table exclude_table.

You can exclude multiple tables...
Code:   

--  Select data from table WHERE not matched in another table
SELECT data_from_table.*
FROM data_from_table
LEFT JOIN exclude_table_1
ON data_from_table.email = exclude_table_1.email
LEFT JOIN exclude_table_2
ON data_from_table.email = exclude_table_2.email
WHERE exclude_table_1.email IS NULL
AND exclude_table_2.email IS NULL;

So, data will be selected where the email address does NOT match an email address in table exclude_table_1 and does NOT match an email address in table exclude_table_2.

For speed, make sure that the field/s you’re comparing in both tables are keys.

MYSQL count email country suffix

This count gives an idea of how much of the email data is from what country.
Code:

SELECT SUBSTRING_INDEX(email, '.', -1) as Suffix, count(email) as Total
FROM members
WHERE (email != '' AND email NOT NULL)
GROUP BY Suffix
ORDER BY Total DESC
LIMIT 100;

You can remove the LIMIT so that you get a full count.

Tunneling Remote Desktop over SSH in Windows 7

This was a difficult one because no matter what I tried, I could not get Remote Desktop to work over SSH (PuTTY) from a Windows 7 system. I just kept getting an error message that a Remote Desktop was already open.

After a lot of Red Herring websites http://blog.spencerkellis.net/2010/06/tunneling-remote-desktop-over-ssh-in-windows-7/ gave the answer.

Windows 7 blocks 3389 and 3390 from being used on the loopback interface. Answer was to use 3391 as the localhost port.

Take a look at that guys excellent instructions for setting up PuTTY SSH on your local system to then tunnel MS Remote Desktop connection via an encrytped SSH connection.

Categories: How-To, Microsoft, OS
Tags: Microsoft, Server, copssh, putty, remote desktop, SH, tunnel, Windows 7

MySQL count of Geographical postcode selections, grouped

Here is an example for a query to give you a count of members/customers/recipients within a postcode2 area, listed (group by) the postcode2 area.

What do I mean? I wanted to know how many club members were in different England postcode areas thus:

Quote:   

POSTCODE2 COUNT
CA 34
CB 12
...
HP 22
..
MK 62

use this

Code:   

-- England count grouped by postcode2
SELECT SUBSTRING(postcode,1,2) AS POSTCODE2, COUNT(*) AS COUNT
FROM members
WHERE (SUBSTRING(postcode,1,2) IN
('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD','BN','CM','CT','GU','HP','LU','ME','MK',
'PO','RG','RH','SG','SL','SO','SS','TN','CB','CO','DE','DN','IP','LE','LN','NG','NR','PE','CV','DY','HR','NN','ST','TF','WR','WS',
'WV','DH','DL','HG','HU','LS','NE','SR','TS','WF','YO','BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA',
'WN','BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]')
OR postcode REGEXP ('^S[1-9]')
OR postcode REGEXP ('^B[1-9]')
OR postcode REGEXP ('^L[1-9]')
OR postcode REGEXP ('^M[1-9]'))
GROUP BY SUBSTRING(postcode,1,2);