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
replaceSQL_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;
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
Command to generate self-signed certificate
keytool -genkeypair -alias apisecurity -keyalg RSA -keysize 2048 -storetype PKCS12 -keystore apisecurity.p12 -validity 3650