api-security-course.md

API Security

SQL Injection

Fix for stored procedure

CREATE OR REPLACE PROCEDURE public.update_jdbc_customer(
  existing_customer_id bigint,
  new_full_name character varying)
LANGUAGE 'plpgsql'
AS $BODY$
begin
  update jdbc_customer set full_name = new_full_name 
   where customer_id = existing_customer_id;	
end
$BODY$;

SQL injection filter full regex

replace SQL_REGEX constant with this

private static final String SQL_TYPES = "TABLE, TABLESPACE, PROCEDURE, FUNCTION, TRIGGER, KEY, VIEW, MATERIALIZED VIEW, LIBRARY"
		+ "DATABASE LINK, DBLINK, INDEX, CONSTRAINT, TRIGGER, USER, SCHEMA, DATABASE, PLUGGABLE DATABASE, BUCKET, "
		+ "CLUSTER, COMMENT, SYNONYM, TYPE, JAVA, SESSION, ROLE, PACKAGE, PACKAGE BODY, OPERATOR"
		+ "SEQUENCE, RESTORE POINT, PFILE, CLASS, CURSOR, OBJECT, RULE, USER, DATASET, DATASTORE, "
		+ "COLUMN, FIELD, OPERATOR";

private static final String[] SQL_REGEX = { "(?i)(.*)(\\b)+SELECT(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+INSERT(\\b)+\\s.*(\\b)+INTO(\\b)+\\s.*(.*)", "(?i)(.*)(\\b)+UPDATE(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+DELETE(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)", "(?i)(.*)(\\b)+UPSERT(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+SAVEPOINT(\\b)+\\s.*(.*)", "(?i)(.*)(\\b)+CALL(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+ROLLBACK(\\b)+\\s.*(.*)", "(?i)(.*)(\\b)+KILL(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+CREATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+ALTER(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+TRUNCATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+LOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+UNLOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+RELEASE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
		"(?i)(.*)(\\b)+DESC(\\b)+(\\w)*\\s.*(.*)", "(?i)(.*)(\\b)+DESCRIBE(\\b)+(\\w)*\\s.*(.*)" };

Spring validator annotation

@NotNull : field value must not null
@AssertTrue : field value must be true
@Size : field must has a size between the attributes min and max (can use only one of min/max), can be applied to String, Collection, Map, and array
@Min : numeric field value must be larger than annotation attribute
@Max : numeric field value must be smaller than annotation attribute
@Email : field value is valid email address
@NotEmpty : field value must not null or empty, can be applied to String, Collection, Map or array
@NotBlank : text value only, field value must not null or whitespace
@Positive : numeric value only, must be positive
@PositiveOrZero : numeric value only, must be positive or zero
@Negative : numeric value only, must be negative
@NegativeOrZero : numeric value only, must be negative or zero
@Past : date value only, the value must be in the past
@PastOrPresent : date value only, the value must be in the past or current (present)
@Future : date value only, the value must be in the future
@FutureOrPresent : date value only, the value must be in the future or current (present)

Create Postgresql user with limited access

DO
$$BEGIN
IF EXISTS (SELECT rolname FROM pg_roles WHERE rolname = 'postgres_limited') THEN
    EXECUTE 'DROP OWNED BY postgres_limited cascade';
END IF;
END$$;

DROP USER IF EXISTS postgres_limited;
CREATE USER postgres_limited WITH PASSWORD 'postgres_limited';
GRANT CONNECT ON DATABASE postgres TO postgres_limited;
GRANT USAGE ON SCHEMA public TO postgres_limited;
GRANT SELECT, INSERT, UPDATE, DELETE ON jdbc_customer, jdbc_merchant, jpa_customer TO postgres_limited;

---

XSS (Cross Site Scripting)

Greeting content

Does not contains xss:

Anna

Contains xss:

Anna <img src='x' onerror='alert("This is XSS")'>

Article content

Does not contains xss:

The sun <h3>is</h3> a star.

Contains xss:

We have so many stars in the galaxy. <img src='' onerror='alert("This is XSS in article")'/>

XSS Request Filter regular expression

private static final String[] XSS_REGEX = {
	"onclick|onkeypress|onkeydown|onkeyup|onerror|onchange|onmouseover|onmouseout|onblur|onselect|onfocus",
	"<\s*script\b[^>]*>(.*?)<\s*/script\b[^>]*>", "script\s+src\s*=", "<\s*script\b[^>]*>",
	"<\s*/script\b[^>]*>", "javascript.*:" };

Content-Security-Policy reference

---

Data Transmission (https)

Command to generate self-signed certificate

keytool -genkeypair -alias apisecurity -keyalg RSA -keysize 2048 -storetype PKCS12 -keystore apisecurity.p12 -validity 3650

---

Cross Origin Resource Sharing

• CORS Documentation