Do you like SQLite ?

just started using it a bit more often … https://www.sqlitetutorial.net/

Query

sqlite3 /path/to/db “select date,time,ip from table where ip=‘1.2.3.4’ limit 100;

.schema

show the database schema and indexes

sqlite> .schema
CREATE TABLE attack(
  "date" TEXT,
  "time" TEXT,
  "ip" TEXT,
  "asnr" TEXT,
  "ascountry" TEXT,
  "asdesc" TEXT,
  "port" TEXT,
  "proto" TEXT,
  "server" TEXT,
  "type" TEXT,
  "method" TEXT,
  "pass" TEXT,
  "user" TEXT,
  "ver" TEXT
);
CREATE UNIQUE INDEX id on attack(date,time,ip,user,pass);

Insert

INSERT INTO table (column1,column2 ,..)
VALUES( value1, value2 ,...);

Update

 update attack set asnr='$asnr', ascountry='$ascountry', asdesc='$asdesc' where ip='$ip';

Delete

delete from attack where ip='$ip';

Transfer one Table from one host to another

ssh remotehost "sqlite3 /var/db/egal.sqlite '.dump table'" |sqlite3 mydb.sqlite

HamsterDB Cleanup

sqlite3 /var/db/runden.db
delete from runden where datum like '%2020-%';
vacuum;

Any Comments ?

sha256: f9dddde1c1140ca0a8fecf1baf3a673ecfadc7d29b2c7a9ee6e33f3c52c6fd1b

BorgBackup

how do you backup your data … ? a really cool solution is borgbackup.

BorgBackup (short: Borg) gives you:

Space efficient storage of backups
Secure, authenticated encryption.
Compression: LZ4, zlib, LZMA, zstd (since borg 1.1.4).
Easy installation on multiple platforms: Linux, macOS, BSD, …
Free software (BSD license).
Backed by a large and active open source community.

Always a good Idea is to keep a Backup external. Rsync.net has a really competitve Offer (without Support). 100 GB for $18/year.

Did you know that you can easily forward a variable (or secret) via SSH … ?

This Variable is just available if you’re logged in and never stored in any configfile or backup. This can be a real advantage …

Sending Host

/etc/ssh/ssh_conf

Host trustedhost.world
  SendEnv        _secret

Receiving Host

/etc/ssh/sshd_conf

AcceptEnv               _secret

restart sshd

Connect

user@myhost ~# export _secret=topsecret99
user@myhost ~# ssh trustedhost.world

user@trustedhost ~# set |grep _secret
_secret=topsecret99

here we are …

Any Comments ?

sha256: 2713843b09025791c3a22c831d592af5ed0a0d7a0e593e67175956f7ee8acfbe

Sometimes you run into problem because you’re not aware of the environment of cron.

So, let’s dump and check the environment.

Add Cronjob

*       *       *       *       *       env > /tmp/env.log

Dump Content

puffy200# cat /tmp/env.log
LOGNAME=root
HOME=/var/log
PWD=/var/log
PATH=/bin:/sbin:/usr/bin:/usr/sbin
SHELL=/bin/sh
USER=root

Any Comments ?

sha256: 6e5d4767a577cc5673505edd19b29f931ce23de7f97a9088c3137945206730a3

Python PIP

https://pip.pypa.io/en/stable/cli/pip_list/

how to PIP with OpenBSD …

Already Installed ?

doas pkg_info -Q py3-pip

py3-pip-20.1.1p0 (installed)

Install pip3

doas pkg_add py3-pip--
doas ln -sf /usr/local/bin/pip3.9 /usr/local/bin/pip

Search

doas pip search csvkit

Install csvkit

doas pip install wheel csvkit

Upgrade pip

doas pip install --upgrade pip

Upgrade pip packages

for i in $(pip list -o | awk 'NR > 2 {print $1}'); do doas pip install -U $i; done

or

doas pip install pip-review
doas pip-review --interactive

Any Comments ?

sha256: 39b0c97b5063483f3d42fd6ac5515f679180cb454d35cff5ee487a19f0fb5343

… and you thought that cgi is dead …

nginx.conf

cat << 'EOF' > /etc/nginx/nginx.conf
worker_processes  1;

worker_rlimit_nofile 1024;
events {
    worker_connections  800;
}

http {
    include       mime.types;
    default_type  application/octet-stream;
    index         index.html index.htm;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  logs/access.log  main;
    access_log  syslog:server=unix:/dev/log,severity=notice main;

    keepalive_timeout  65;

    server_tokens off;

    server {
        listen       80;
        listen       [::]:80;
        server_name  localhost;
        root         /var/www/htdocs;

        # FastCGI to CGI wrapper server
        #
        location /cgi-bin/ {
            #error_log     /var/log/slowcgi/errors;
            fastcgi_pass   unix:run/slowcgi.sock;
            fastcgi_split_path_info ^(/cgi-bin/[^/]+)(.*);
            fastcgi_param  PATH_INFO $fastcgi_path_info;
            include        fastcgi_params;
        }
    }
}
'EOF'

chmod 644 /etc/nginx/nginx.conf
rcctl enable nginx
rcctl start nginx

Slowcgi

rcctl enable slowcgi
rcctl start slowcgi

CGI

cat << 'EOF' > /var/www/cgi-bin/test.cgi
#!/bin/sh

echo "Content-type: text/html\n\n";
echo "<HTML>\n";
echo "<HEAD>\n";
echo "  <title>Ich bin ein Titel :)</title>\n";
echo "</HEAD>\n";
echo "Test from /bin/sh ..!\n";
echo "</HTML>\n";
EOF

chown www /var/www/cgi-bin/test.cgi
chmod 500 /var/www/cgi-bin/test.cgi

Install Interpreter (Chrooted !)

mkdir /var/www/bin/
cp /bin/sh /var/www/bin/

Test

curl http://ip-of-device/cgi-bin/test.cgi

Troubleshoot

chroot /var/www/ cgi-bin/test.cgi

Any Comments ?

sha256: cb939fe359ec8b8611392b03c702d42de819c4a51b81c120a70fe4a8d7ff6770

… and you thought that cgi is dead …

httpd.conf

cat << 'EOF' > /etc/httpd.conf
types {
  include "/usr/share/misc/mime.types"
}


## A minimal default server ##
server "default" {
  listen on *   port 80
  log { access "default-access.log", error "default-error.log" }
  location "/cgi-bin/*" {
    fastcgi socket "/run/slowcgi.sock"
    root "/"
  }
}
EOF

chmod 644 /etc/httpd.conf
rcctl enable httpd
rcctl start httpd

Slowcgi

rcctl enable slowcgi
rcctl start slowcgi

CGI

cat << 'EOF' > /var/www/cgi-bin/test.cgi
#!/bin/sh

echo "Content-type: text/html\n\n";
echo "<HTML>\n";
echo "<HEAD>\n";
echo "  <title>Ich bin ein Titel :)</title>\n";
echo "</HEAD>\n";
echo "Test from /bin/sh ..!\n";
echo "</HTML>\n";
EOF

chown www /var/www/cgi-bin/test.cgi
chmod 500 /var/www/cgi-bin/test.cgi

Install Interpreter (Chrooted !)

mkdir /var/www/bin/
cp /bin/sh /var/www/bin/

Test

curl http://ip-of-device/cgi-bin/test.cgi

Any Comments ?

sha256: c102990dbf0d3903c8a066e7add79f0d1cac8b99557fb01874b2708d0135b710

OpenBSD Current

Active OpenBSD development is known as the -current branch. These sources are frequently compiled into releases known as snapshots FAQ

Assuming, you can’t wait for the next release, or you wanna test features, find bugs and so participate on the community, this little script will help you:

Upgrade to Current

and remove game*,comp*,xf* and xs* Packages before reboot

cat << 'EOF' > upgrade_to_current.sh
#!/bin/sh

echo "let's check for news ..."

local _response=$(sysupgrade -n -s)

if [[ $_response == *reboot ]]; then
  echo "\nInstalled! Let's reboot ...\n"
  rm /home/_sysupgrade/{game,comp,xf,xs}*
  reboot
else
  echo "Nothing todo ..."
fi

exit 0
EOF

chmod 755 upgrade_to_current.sh

Any Comments ?

sha256: c6eb0b5142102775f26c373f1d16c378ade7683af62ca77bb3d088fdbb52c603

doas, an alternative to sudo

Everybody knows sudo … right ? but the openbsd guys hacked a small and secure replacement called doas …

simple, secure and clever

here a good and quick tutorial

An introduction on Vultr, the Source Code on Github and the Man Page

Installation OpenBSD

On OpenBSD, it’s already in the Base System and no need to install anything.

Installation Linux

On Linux, for Example, you have to add the Package

ssh-audit is a tool for ssh server auditing.

Features

SSH1 and SSH2 protocol server support;

grab banner, recognize device or software and operating system, detect compression;

gather key-exchange, host-key, encryption and message authentication code algorithms;

output algorithm information (available since, removed/disabled, unsafe/weak/legacy, etc);

output algorithm recommendations (append or remove based on recognized software version);

output security information (related issues, assigned CVE list, etc);

analyze SSH version compatibility based on algorithm information;