MS SQL full database search procedure

This is a neat procedure to search for a string in all MS SQL database:

CREATE PROCEDURE Find_My_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM Information_Schema.Columns AS C
    INNER JOIN Information_Schema.Tables AS T
        ON C.Table_Name = T.Table_Name
            AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE Table_Type = 'Base Table'
        AND Data_Type IN ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
    THEN 'IF EXISTS(SELECT *
            FROM ReplaceTableName
            WHERE CONVERT(nVarChar(4000), [ReplaceColumnName])
                = ''' + @DataToFind + '''
            )
            SET @DataExists = 1
        ELSE
            SET @DataExists = 0'
    ELSE 'IF EXISTS(SELECT *
            FROM ReplaceTableName
            WHERE CONVERT(nVarChar(4000), [ReplaceColumnName])
                LIKE ''%' + @DataToFind + '%''
            )
            SET @DataExists = 1
        ELSE
            SET @DataExists = 0'
    END,

@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM @Temp
        WHERE RowId = @i

        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
        BEGIN
            PRINT @SQL
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
        END

        SET @i = @i + 1
    END

SELECT SchemaName, TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO

After you created this procedure, do a search with the following:

exec [dbo].[Find_My_String] 'searchstring', 0

 

Useful MySQL queries

Create MySQL database with character set and collate:

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

 

Windows system integrity checks

Here are some useful commands to check windows system integrity. First of all, launch a command prompt with privileged permissions:

  • Click Start
  • Type cmd
  • Right click on “cmd” and
  • Select “Run as Administrator”

First of all, lets run System File Checker:

> sfc /scannow

Next lets run Deployment Image Servicing and Management tool. Let’s check first:

> Dism /Online /Cleanup-Image /CheckHealth
> Dism /Online /Cleanup-Image /ScanHealth

Next commands are for repairing. If you have your installation ESD or WIM file – that’s even better. Here’s how you can check and restore from ESD and WIM images accordingly:

> Dism /Online /Cleanup-Image /RestoreHealth /Source:esd:D:\install.esd:1
> Dism /Online /Cleanup-Image /RestoreHealth /Source:wim:D:\install.wim:1

To prevent DISM from using Windows Update for online images, you can run these (ESD and WIM):

> Dism /Online /Cleanup-Image /RestoreHealth /Source:esd:D:\install.esd:1 /LimitAccess
> Dism /Online /Cleanup-Image /RestoreHealth /Source:wim:D:\install.wim:1 /LimitAccess

 

That’s it for now.

HA Cluster: DRBD software

Install required repositories and DRBD software:

$ rpm --import https://www.elrepo.org/RPM-GPG-KEY-elrepo.org
$ rpm -Uvh http://www.elrepo.org/elrepo-release-7.0-2.el7.elrepo.noarch.rpm
$ yum -y install drbd84-utils kmod-drbd84

Configure SElinux to allow DRBD to work correctly:

$ setsebool -P daemons_enable_cluster_mode 1

Create SElinux module, build and install it:

$ vi /root/drbd_modprobe.te
module drbd_modprobe 1.4;

require {
    type drbd_t;
    type fixed_disk_device_t;
    type fs_t;
    type modules_conf_t;
    type tmp_t;
    type var_lock_t;
    type var_run_t;
    class blk_file write;
    class dir { write remove_name getattr read open search add_name };
    class file { write lock create unlink getattr read open };
    class filesystem getattr;
    class lnk_file { create unlink };
}

#============= drbd_t ==============

allow drbd_t fixed_disk_device_t:blk_file write;
allow drbd_t fs_t:filesystem getattr;
allow drbd_t modules_conf_t:dir { getattr read open search };
allow drbd_t modules_conf_t:file { getattr read open };
allow drbd_t tmp_t:file { create write unlink open };
allow drbd_t var_lock_t:file { lock open read write };
allow drbd_t var_run_t:dir { write remove_name add_name };
allow drbd_t var_run_t:lnk_file { create unlink };
$ checkmodule -M -m -o /root/drbd_modprobe.mod /root/drbd_modprobe.te
$ semodule_package -o /root/drbd_modprobe.pp -m /root/drbd_modprobe.mod
$ semodule -i /root/drbd_modprobe.pp

Continue reading “HA Cluster: DRBD software”

HA cluster on centos

HA (High Availability) cluster is used to increase availability for any selected service on linux. Today I will show you how to install and configure basic software on Centos 7 – to get started with HA Cluster. First of all, we need two Centos 7 nodes installed in server mode and fully updated. For this example I will use these IP addresses as reference:

  1. Node1 – 10.0.0.1
  2. Node2 – 10.0.0.2

All commands from this point forward are run as root and on both nodes unless noted otherwise.

First lets install all the software we will need:

$ yum -y install corosync pacemaker pcs

Configure firewalld daemon to allow communications between cluster nodes:

$ firewall-cmd --permanent --zone=public --add-service=high-availability
$ firewall-cmd --reload

Set password for hacluster user, used for inter-node communications. Here you are asked twice to enter new password:

$ passwd hacluster

Enable and start pacemaker daemon:

$ systemctl enable pcsd.service
$ systemctl start pcsd.service

(Only Node1) Authenticate both nodes to a cluster (here we use the same hacluster username and password set a few steps back):

$ pcs cluster auth node1 node2

(Only Node1) Setup, enable and start cluster software:

$ pcs cluster setup --name hacluster node1 node2
$ pcs cluster enable --all
$ pcs cluster start --all

Continue reading “HA cluster on centos”

Usefull openssl commands

Generate AWS private key’s fingerprint using openssl:

$ openssl.exe pkcs8 -in private_key.pem -inform PEM -outform DER -topk8 -nocrypt | openssl sha1 -c

Check if certificate and intermediate match each other:
Execute following and look for “X509v3 Authority Key Identifier”

$ openssl x509 -in certificate.pem -noout -text

Execute following and look for “X509v3 Subject Key Identifier”

$ openssl x509 -in intermediate.pem -noout -text

 

Check Certificate agains Private Key

$ openssl x509 -in server.crt -noout -modulus | openssl md5
$ openssl rsa -in server.key -noout -modulus | openssl md5

This should output two md5 hashes. If they are identical, then Certificate matches Private Key.

 

Create PKCS12 from Private Key, Certificate and Chain file:

$ openssl pkcs12 -export  -inkey your_private_key.key -in your_certificate.cer -certfile your_chain.pem -out final_result.pfx

And a reverse process to extract Private Key and Certificate with Chain from PFX | PKCS12:

$ openssl pkcs12 -in certname.pfx -nocerts -nodes -out key.pem
$ openssl pkcs12 -in certname.pfx -nokeys -out cert.pem
$ openssl rsa -in key.pem -out key_clean.key

Bypass strict SSH host key checking

An SSH command has two useful parameters:
* Strict Host Key Checking
* User Known Hosts File

This little workaround came in handy a few times already. It shouldn’t be used always, as it a security check and it definitly shouldn’t be ignored. So a good example where it might be used, if you have a HA Cluster with a virtual IP which may flip between hosts and you have to SSH into that IP. Here’s the command:

$ ssh -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null user@server.hostname.or.ip

Apache2 client IP logging behind AWS ELB

Came to a problem a few days ago. If an EC2 instance is behind an ELB (Elastic Load Balancer) then in apache logs all you will see is ELB’s internal IP addresses. This post might be interesting to AWS users hosting their site on EC2 with apache and wanting to see client IP addresses in apache logs. Let’s assume we are using Amazon Linux with httpd daemon. Here is what you have to do:

1st – enable mod_remoteip (actually should be enabled by default). You can check it with this:

$ apachectl -M | grep 'remoteip'

The output should look something like this:

 remoteip_module (shared)

Then you need to modify httpd.conf file:

$ vi /etc/httpd/conf/httpd.conf

And add these lines just before first LogFormats directive:

<IfModule mod_remoteip.c>
        RemoteIPHeader X-Forwarded-For
        RemoteIPInternalProxy 172.31.0.0/16
</IfModule>

Also in the same file you should change any LogFormat directive and replace %h with %a, here’s an example. Before:

LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined

After:

LogFormat "%a %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined

More information can be found in apache documentation:
http://httpd.apache.org/docs/2.4/mod/mod_log_config.html#formats
https://httpd.apache.org/docs/trunk/mod/mod_remoteip.html

A4s Addons

This will contain information about my own created wordpress plugin with interesting and valuable features.