Relational and Non-relational Databases
From Gridkaschool
Jump to navigationJump to search
Overview
Author: Mario Lassnig, CERN PH-ADP-CO, [1]
PostgreSQL (Setup)
vim /var/lib/pgsql/9.4/data/pg_hba.conf
set all to trust
add line
host all all 0.0.0.0/0 trust
psql -U gridka01 gridka_db
should not ask for password ctrl-d to exit
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE ROLE gridka01 PASSWORD 'asdf1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;" sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE DATABASE gridka_db OWNER gridka01 ENCODING 'UTF8'" sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "CREATE SCHEMA gridka_schema AUTHORIZATION gridka01" sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON SCHEMA gridka_schema TO gridka01" sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON DATABASE gridka_db TO gridka01"
vim /var/lib/pgsql/9.4/data/pg_hba.conf
set all to md5
systemctl restart postgresql-9.4.service
psql -U gridka01 gridka_db
should ask for password
ldconfig
ls -la /var/lib/pgsql/9.4/data
PostgreSQL (SQL)
psql -U gridka01 gridka_db
\l .. list all databases \d .. list all relations
CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR );
\d
SELECT * FROM test_table;
INSERT INTO test_table VALUES ('Vincent', 'Vega');
SELECT * FROM test_table;
INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');
SELECT * FROM test_table;
DELETE FROM test_table;
SELECT * FROM test_table;
DROP TABLE test_table;
SELECT * FROM test_table;
PostgreSQL (C)
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
int main()
{
PGconn *conn;
conn = PQconnectdb("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'");
if (PQstatus(conn) != CONNECTION_OK) {
printf("%s\n", PQerrorMessage(conn));
exit(1);
}
PGresult *res;
res = PQexec(conn, "CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )");
if (!res)
printf("%s\n", PQerrorMessage(conn));
PQclear(res);
res = PQexec(conn, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )");
if (!res)
printf("%s\n", PQerrorMessage(conn));
PQclear(res);
res = PQexec(conn, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )");
if (!res)
printf("%s\n", PQerrorMessage(conn));
PQclear(res);
res = PQexec(conn, "SELECT * FROM test_table");
if (PQresultStatus(res) == PGRES_TUPLES_OK)
for(int i = 0; i < PQntuples(res); ++i)
printf("%s %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));
else
printf("%s\n", PQerrorMessage(conn));
PQclear(res);
res = PQexec(conn, "DELETE FROM test_table");
if (!res)
printf("%s\n", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
}
gcc -g -std=c11 -Wall -Wextra -I$(/usr/pgsql-9.4/bin/pg_config --includedir) -L$(/usr/pgsql-9.4/bin/pg_config --libdir) -lpq test_pg.c -o test_pg
PostgreSQL (Python)
import psycopg2 as pg
conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'")
cur = conn.cursor()
cur.execute("CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )")
cur.execute("INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )")
conn.commit()
cur.execute("SELECT * FROM test_table")
cur.fetchall()
cur.execute("INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )")
conn.commit()
cur.execute("SELECT * FROM test_table")
cur.fetchall()
cur.execute("DROP TABLE test_table")
conn.commit()
MonetDB (Setup)
monetdbd create /tmp/gridka_schema monetdbd start /tmp/gridka_schema monetdb create gridka_db monetdb release gridka_db
MonetDB (SQL)
mclient -u monetdb -d gridka_db (default password: monetdb)
CREATE USER "gridka01" WITH PASSWORD 'asdf1234' NAME 'gridka01' SCHEMA "sys";
CREATE SCHEMA "gridka01" AUTHORIZATION "gridka01";
ALTER USER "gridka01" SET SCHEMA "gridka01";
CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) );
INSERT INTO test_table VALUES ('Vincent', 'Vega');
INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');
SELECT * FROM test_table;
DELETE FROM test_table;
DROP test_table;
MonetDB (C)
#include <stdio.h>
#include <stdlib.h>
#include <mapi.h>
int main()
{
Mapi db;
db = mapi_connect("localhost", 50000,
"gridka01", "asdf1234",
"sql", "gridka_db");
if (mapi_error(db))
mapi_explain(db, stderr);
MapiHdl res;
res = mapi_query(db, "CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )");
if (mapi_error(db))
mapi_explain(db, stderr);
mapi_close_handle(res);
res = mapi_query(db, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )");
if (mapi_error(db))
mapi_explain(db, stderr);
mapi_close_handle(res);
res = mapi_query(db, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )");
if (mapi_error(db))
mapi_explain(db, stderr);
mapi_close_handle(res);
res = mapi_query(db, "SELECT * FROM test_table");
if (mapi_error(db))
mapi_explain(db, stderr);
while(mapi_fetch_row(res)) {
printf("%s %s\n", mapi_fetch_field(res, 0), mapi_fetch_field(res, 1));
}
mapi_close_handle(res);
res = mapi_query(db, "DELETE FROM test_table");
if (mapi_error(db))
mapi_explain(db, stderr);
mapi_close_handle(res);
mapi_destroy(db);
}
gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs monetdb-mapi) monetdb_test.c -o monetdb_test
MonetDB (Python)
import monetdb.sql as mo
conn = mo.connect(database='gridka_db', username='gridka01', hostname='localhost', password='asdf1234')
cur = conn.cursor()
cur.execute("CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )")
cur.execute("INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )")
conn.commit()
cur.execute("SELECT * FROM test_table")
cur.fetchall()
cur.execute("INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )")
conn.commit()
cur.execute("SELECT * FROM test_table")
cur.fetchall()
cur.execute("DROP TABLE test_table")
conn.commit()
LevelDB (C)
mkdir gridka_db
</nowiki>
- include <iostream>
- include <string>
- include <leveldb/db.h>
int main() {
leveldb::DB* db;
leveldb::Options opts;
leveldb::ReadOptions ropts;
leveldb::WriteOptions wopts;
leveldb::Status status;
leveldb::Iterator* it;
opts.create_if_missing = true;
status = leveldb::DB::Open(opts, "gridka_db/test_table", &db);
status = db->Put(wopts, "0", "{\"first_name\": \"Vincent\", \"last_name\": \"Vega\"}");
status = db->Put(wopts, "1", "{\"first_name\": \"Jules\", \"last_name\": \"Winnfield\"}");
std::cout << db->Get(ropts, std::string("0"), &db) << '\n';
it = db->NewIterator(ropts);
for(it->SeekToFirst(); it->Valid(); it->Next())
std::cout << it->key().ToString() << '\t' << it->value().ToString() << '\n';
delete it;
status = db->Delete(wopts, "0");
status = db->Delete(wopts, "1");
delete db;
} </nowiki>
g++ -g -std=c++14 -Wall -Werror -pedantic -lleveldb leveldb_test.cpp -o leveldb_test
LevelDB (Python)
import json
import leveldb
db = leveldb.LevelDB('gridka_db/test_table')
db.Put('0', json.dumps({'first_name': 'Vincent', 'last_name': 'Vega'}))
db.Put('1', json.dumps({'first_name': 'Jules', 'last_name': 'Winnfield'}))
json.loads(db.Get('0'))
it = db.RangeIter()
[(x[0],json.loads(x[1])) for x in it]
db.Delete('0')
db.Delete('1')
Redis (Setup)
vim /etc/redis/redis.conf requirepass asdf1234 service redis-server restart
Redis (Native)
redis-cli auth asdf1234
set test_table:0 '{"first_name": "Vincent", "last_name": "Vega"}'
set test_table:1 '{"first_name": "Jules", "last_name": "Winnfield"}'
get test_table:0
hset test_table:0 first_name Vincent hset test_table:0 last_name Vega hset test_table:1 first_name Jules hset test_table:1 last_name Winnfield
hget test_table:0 first_name hget test_table:1 last_name
del test_table:0 del test_table:1
Redis (C)
#include <stdio.h>
#include <stdlib.h>
#include <hiredis.h>
int main()
{
redisContext *ctx;
redisReply *r;
ctx = redisConnect("localhost", 6379);
if (ctx->err)
printf("error: %s\n", ctx->errstr);
r = redisCommand(ctx, "auth asdf1234");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "hset test_table:0 first_name Vincent");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "hset test_table:0 last_name Vega");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "hset test_table:1 first_name Jules");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "hset test_table:1 last_name Winnfield");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "hget test_table:0 first_name");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
else
printf("%s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "hget test_table:1 last_name");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
else
printf("%s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "del test_table:0");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
r = redisCommand(ctx, "del test_table:1");
if (r->type == REDIS_REPLY_ERROR)
printf("error: %s\n", r->str);
freeReplyObject(r);
redisFree(ctx);
}
gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs hiredis) redis_test.c -o redis_test
Redis (Python)
import redis
db = redis.StrictRedis(password='asdf1234')
db.set('test_table:0', '{"first_name": "Vincent", "last_name": "Vega"}')
db.set('test_table:1', '{"first_name": "Jules", "last_name": "Winnfield"}')
db.hset('test_table:0', 'first_name', 'Vincent')
db.hset('test_table:0', 'last_name', 'Vega')
db.hset('test_table:1', 'first_name', 'Jules')
db.hset('test_table:1', 'last_name', 'Winnfield')
db.delete('test_table:0')
db.delete('test_table:1')
MongoDB (Setup)
semanage port -a -t mongod_port_t -p tcp 27017 systemclt restart mongod chkconfig mongod on
mongo
show dbs
use admin
db.createUser({"user": "admin", "pwd": "admin", "roles": ["userAdminAnyDatabase"]})
use gridka_db
show collections
db.createUser({"user": "gridka01", "pwd": "asdf1234", "roles": ["readWrite"]})
vim /etc/mongod.conf
set auth=true
service mongod restart
MongoDB (native)
mongo gridka_db
db.test_table.insert({"first_name": "Vincent", "last_name": "Vega"})
mongo -u gridka01 -p asdf1234 gridka_db
db.test_table.insert({"first_name": "Vincent", "last_name": "Vega"})
db.test_table.insert({"first_name": "Jules", "last_name": "Winnfield"})
db.test_table.find()
db.test_table.insert({"you_can_put": "whatever you want"})
db.test_table.find()
db.test_table.drop()
MongoDB (C)
#include <stdio.h>
#include <stdlib.h>
#include <bson.h>
#include <mongoc.h>
int main ()
{
mongoc_client_t *client;
mongoc_collection_t *collection;
bson_error_t error;
bson_t *doc;
bson_t *query;
mongoc_cursor_t *cursor;
char *str;
mongoc_init();
client = mongoc_client_new("mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db");
collection = mongoc_client_get_collection (client,
"gridka_db",
"test_table");
doc = BCON_NEW("first_name",BCON_UTF8("Vincent"),
"last_name", BCON_UTF8("Vega"));
if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE,
doc, NULL, &error))
printf("error: %s\n", error.message);
bson_destroy(doc);
doc = BCON_NEW("first_name", BCON_UTF8("Jules"),
"last_name", BCON_UTF8("Winnfield"));
if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE,
doc, NULL, &error))
printf("error: %s\n", error.message);
bson_destroy(doc);
query = bson_new();
cursor = mongoc_collection_find(collection, MONGOC_QUERY_NONE,
0, 0, 0, query, NULL, NULL);
while(mongoc_cursor_next(cursor, &doc)) {
str = bson_as_json(doc, NULL);
printf("%s\n", str);
bson_free(str);
}
bson_destroy(query);
mongoc_cursor_destroy(cursor);
if(!mongoc_collection_drop(collection, &error))
printf("error: %s\n", error.message);
mongoc_collection_destroy(collection);
mongoc_client_destroy(client);
}
gcc -g -std=c11 -Wall -Wextra -pedantic $(pkg-config --cflags --libs libmongoc-1.0) mongo_test.c -o mongo_test
MongoDB (Python)
from pymongo import MongoClient
client = MongoClient('mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db')
db = client.gridka_db
db.test_table.insert_one({'first_name': 'Vincent', 'last_name': 'Vega'})
db.test_table.insert_one({'first_name': 'Jules', 'last_name': 'Winnfield'})
[x for x in db.test_table.find()]
db.test_table.insert({'first_name': 'Marcellus', 'occupation': 'businessman'})
[x for x in db.test_table.find()]
db.test_table.drop()
Neo4j (Native)
http://localhost:7474/browser/
CREATE (id_0:pulp_fiction {first_name: 'Vincent', last_name: 'Vega'})
CREATE (id_1:pulp_fiction {first_name: 'Jules', last_name: 'Winnfield'})
CREATE (id_0)-[:TELLS_STORY {about: 'le big mac'}]->(id_1)
CREATE (id_1)-[:TELLS_STORY {about: 'foot massage'}]->(id_0)
MATCH (n) RETURN (n) MATCH (n) OPTIONAL MATCH (n)-[r]->() DELETE n,r
Neo4j (Python)
from py2neo import Graph, Node, Relationship
graph = Graph("http://neo4j:asdf1234@localhost:7474/db/data/")
id_0 = Node("pulp_fiction", first_name='Vincent', last_name='Vega')
id_1 = Node("pulp_fiction", first_name='Jules', last_name='Winnfield')
graph.create(id_0)
graph.create(id_1)
graph.create(Relationship(id_0, 'TELLS_STORY', id_1, about='le big mac'))
graph.create(Relationship(id_1, 'TELLS_STORY', id_0, about='foot massage'))
Query plans
#!/usr/bin/python
import random
import time
import psycopg2 as pg
conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'")
conn.autocommit=False
cur = conn.cursor()
try: cur.execute('drop table large_table')
except: pass
conn.commit()
cur.execute('create table large_table (l char, s varchar(255), n int)')
conn.commit()
conn.autocommit=True
ts = time.time()
for i in xrange(1000000):
if i%100 == 0:
print i
cur.execute("INSERT INTO large_table VALUES ('%s', '%s', %i)" % (chr(random.randrange(65, 91)),
''.join([chr(random.randrange(65, 91)) for i in xrange(1,16)]),
random.randint(0,1000000)))
conn.commit()
print 'time taken:', time.time()-ts
conn.close()
set enable_seqscan=false;
select l, count(*) from large_table group by l; select * from large_table where l='A'; create index idx_l on large_table(l);
create table large_table (l char, s varchar(255), n int)
partition by range (n) (
partition p0 values less than (500000),
partition p1 values less than maxvalue
);
create table large_table (l char, s varchar(255), n int)
partition by hash(n)
partitions 10;
create table large_table (l char, s varchar(255), n int)
partition by list(l) (
partition p0 values in ('A', ... , 'L'),
partition p1 values in ('M, ... , 'Z')
);
Transactions
create table tx_test (name varchar(4), money integer);
insert into tx_test values ('jack', 50);
insert into tx_test values ('jill', 100);
show transaction isolation level;
t1:
begin; select money from tx_test where name='jack';
t2:
begin; update tx_test set money=money-10 where name='jack'; commit;
t1:
select money from tx_test where name='jack';
t1:
set session characteristics as transaction isolation level repeatable read;
t2:
set session characteristics as transaction isolation level repeatable read;
t1:
begin; select money from tx_test where name='jack';
t2:
begin; update tx_test set money=money-10 where name='jack'; commit;
t1:
select money from tx_test where name='jack'; update tx_test set money=money+10 where name='jack';
t1:
set session characteristics as transaction isolation level read committed;
t2:
set session characteristics as transaction isolation level read committed;
t1:
begin;
t2:
begin;
t1:
update tx_test set money=money-10 where name='jack';
t2:
update tx_test set money=money+10 where name='jill';
t1:
update tx_test set money=money+10 where name='jill';
t2:
update tx_test set money=money+10 where name='jack';
SQL Injection
CREATE TABLE users (id integer, login varchar(255), password varchar(255));
CREATE EXTENSION pgcrypto;
INSERT INTO users VALUES (0, 'root', crypt('hunter2', 'sha1'));
SELECT * FROM users;
#!/usr/bin/python
import random
import time
import psycopg2 as pg
conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'")
cur = conn.cursor()
login = raw_input('login: ')
password = raw_input('password: ')
print "select * from users where login=\'%s\' and password=crypt('%s', 'sha1')" % (login, password)
cur.execute("select * from users where login=\'%s\' and password=crypt('%s', 'sha1')" % (login, password))
if cur.fetchone():
print 'logged in'
else:
print 'wrong login or password'
cur.close()
conn.close()
python sql_inject.py
root / hunter2
root / ','sha1') or 1=1; update users set password=crypt('mypass', 'sha1'); commit; select 1;--
Multiprocess
#!/usr/bin/python
import psycopg2 as pg
from multiprocessing import Process
from random import randint
from time import sleep
def get_session():
return pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'")
def bootstrap_tables():
session = get_session()
cur = session.cursor()
try:
print 'trying to create new table'
cur.execute('create table mp_test (value integer)')
except:
print 'table alread existed, truncating instead'
cur.execute('truncate table mp_test')
cur.close()
session.commit()
def insert_rows():
while True:
print 'inserting'
session = get_session()
cur = session.cursor()
cur.execute('insert into mp_test values (%s)', [randint(0,9)])
session.commit()
cur.close()
session.close()
sleep(0.1)
def delete_rows():
while True:
print 'deleting'
session = get_session()
cur = session.cursor()
cur.execute('delete from mp_test where value = %s', [randint(0,9)])
session.commit()
cur.close()
session.close()
sleep(0.1)
def count_rows():
while True:
session = get_session()
cur = session.cursor()
cur.execute('select count(*) from mp_test')
res = cur.fetchone()
print 'rows', res[0]
cur.close()
session.close()
sleep(1)
if __name__ == '__main__':
bootstrap_tables()
p_inserter = Process(target=insert_rows)
p_deleter = Process(target=delete_rows)
p_counter = Process(target=count_rows)
p_inserter.start()
p_deleter.start()
p_counter.start()
p_inserter.join()
p_deleter.join()
p_counter.join()
