Why I use MariaDB instead of MySQL?

1. https://mariadb.com/resources/blog/why-should-you-migrate-mysql-mariadb
2. https://mariadb.com/kb/en/library/mariadb-vs-mysql-features/
3. https://seravo.fi/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql
4. https://dba.stackexchange.com/questions/14/when-is-the-right-time-to-use-mariadb-instead-of-mysql-and-why

Simple Java MariaDB example:

1. https://stackoverflow.com/questions/37909487/how-can-i-connect-to-mariadb-using-java

How to prevent SQL injection attacks?

Using PreparedStatement, you can prevent SQL injection attacks.

try{
    String query = "INSERT INTO TB_USER"
        + "(COLUMN1, COLUMN2, COLUMN3, COLUMN4) VALUES"
        + "(?,?,?,?)";
    PreparedStatement preparedStatement = dbConnection.prepareStatement(query);
    preparedStatement.setString(1, txt_nidn.getText());
    preparedStatement.setString(2, txt_nikdosen.getText());
    preparedStatement.setString(3, txt_namadosen.getText());
    preparedStatement.setString(4, txt_alamat.getText());
    preparedStatement .executeUpdate();
    JOptionPane.showMessageDialog(this,"Data Berhasil Di Simpan","Informasi",JOptionPane.INFORMATION_MESSAGE);
    gettabel();
    bersih();
} catch (SQLException ex){
    JOptionPane.showMessageDialog(null, "Proses Penyimpanan Gagal atau Cek Koneksi Anda!","Error",JOptionPane.ERROR_MESSAGE);
    System.out.println(ex.getMessage());
}

Please don't forget to change TB_USER column name. Replace all COLUMN1, COLUMN2, COLUMN3, COLUMN4 to your tables column name.

All credit goes to Jon Skeet.

Related Link:

1. http://www.javatpoint.com/PreparedStatement-interface
2. http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
3. http://www.java2s.com/Code/Java/Database-SQL-JDBC/InsertRecordsUsingPreparedStatement.htm

Original Link: How I can solve these Column count doesn't match value count at row 1?

Improve PostgreSQL query performance for 100 millions of data

Optimization theory for counting all records:

1. remove the unnecessary field in SELECT query
2. remove ORDER BY ASC/DES portion(saves 7% - 10%)
3. remove aggregate functions(avg, sum, count etc)
4. Use standard VACUUM to reclaim storage occupied by dead tuples.
5. Research the "EXPLAIN ANALYZE [your_query_here]" result from http://explain.depesz.com/

Explanation No. 1: remove unnecessary field in SELECT query

select count(*) from ( SELECT
    HD.URINO
    /*HD.URIBRUI,
    HD.TCODE,
    HD.SQCODE*/
FROM
    TV_HD HD)

Explanation No. 2: remove ORDER BY ASC/DES portion(saves 7% - 10%)

select count(*) from ( SELECT
    HD.URINO
FROM
    TV_HD HD
    /*ORDER BY HD.URINO DESC*/)

Explanation No. 3: remove aggregate functions(avg, sum, count etc)

select count(*) from ( SELECT
    name
    /*MAX(salary),
    AVG(salary)*/
FROM Emp)

Explanation No. 4: Use standard VACUUM to reclaim storage occupied by dead tuples.

VACUUM (VERBOSE, ANALYZE) your_table;

In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM,periodically especially on frequently-updated tables.

There are two variants of VACUUM: standard VACUUM and.VACUUM FULL

VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.) VACUUM FULL requires an exclusive lock on the table it is working on, and therefore cannot be done in parallel with other use of the table.

Generally, therefore, administrators should strive to use andstandard VACUUM avoid VACUUM FULL.

For details:

1. http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
2. http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html

Thanks for your time.

Original Link: Improve PostgreSQL query performance having left join for 100 millions of data

How to get ER model of database from server with Workbench?

Pictorial View is given step by step:

1. Go to "Database" Menu option
2. Select the "Reverse Engineer" option.

3. A wizard will come. Select from "Stored Connection" and press "Next" button.

4. Then "Next"..to.."Finish"

Enjoy :)

Resource Link: https://stackoverflow.com/a/39313205/2293534

SQL Injection and how to prevent it? Hibernet/JPA/SQL

SQL Injection

1.      Prepared Statement and Callable Statement:

A PreparedStatement represents a precompiled SQL statement that can be executed multiple times without having to recompile for every execution.

Secure Code:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?");

stmt.setString(1, userid);

stmt.setString(2, password);

ResultSet rs = stmt.executeQuery();

Why this code is secure?

Ans: This code is not vulnerable to SQL Injection because it correctly uses parameterized queries. By utilizing Java's PreparedStatement class, bind variables (i.e. the question marks) and the corresponding setString methods, SQL Injection can be easily prevented.

Vulnerable Code 1:

// Example #1

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(query);

Why this code is vulnerable?

Ans: This code is vulnerable to SQL Injection because it uses dynamic queries to concatenate malicious data to the query itself. Notice that it uses the Statement class instead of the PreparedStatement class.

Vulnerable Code 2:

// Example #2

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

PreparedStatement stmt = connection.prepareStatement(query);

ResultSet rs = stmt.executeQuery();

Why this code is vulnerable?

Ans: This code is also vulnerable to SQL Injection. Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-using-prepared-callable-statement

2.      Hibernate:

How to Fix SQL Injection using Hibernate?

Hibernate facilitates the storage and retrieval of Java domain objects via Object/Relational Mapping (ORM). It is a very common misconception that ORM solutions, like hibernate, are SQL Injection proof. Hibernate allows the use of "native SQL" and defines a proprietary query language, named, HQL (Hibernate Query Language); the former is prone to SQL Injection and the later is prone to HQL (or ORM) injection.

This article is intended to illustrate how certain syntax offered by hibernate to define SQL & HQL, is better over the other, in terms of defense against SQL and/or HQL injection attacks.

Secure Usage:

Code-1:

/* Positional parameter in HQL */

Query hqlQuery = session.createQuery("from Orders as orders where orders.id = ?");

List results = hqlQuery.setString(0, "123-ADB-567-QTWYTFDL").list();

Code-2:

/* named parameter in HQL */

Query hqlQuery = session.createQuery("from Employees as emp where emp.incentive > :incentive");

List results = hqlQuery.setLong("incentive", new Long(10000)).list();

Code-3:

/* named parameter list in HQL */

List items = new ArrayList();

items.add("book"); items.add("clock"); items.add("ink");

List results = session.createQuery("from Cart as cart where cart.item in (:itemList)").setParameterList("itemList", items).list();

Code-4:

/* JavaBean in HQL */

Query hqlQuery = session.createQuery("from Books as books where book.name = :name and book.author = :author");

List results = hqlQuery.setProperties(javaBean).list();

//assumes javaBean has getName() & getAuthor() methods.

Code-5:

/* Native-SQL */

Query sqlQuery = session.createSQLQuery("Select * from Books where author = ?");

List results = sqlQuery.setString(0, "Charles Dickens").list();

Why above 5 codes are secure ?

Ans:

The above code snippets use parameter binding to set data. The JDBC driver will escape this data appropriately before the query is executed, making sure that data is used just as data.

Assuming data used in the above code snippets is user input, that has not been validated or escaped and it contains malicious database code (payload), the payload will be escaped appropriately by the JDBC driver (since parameterized queries are used), such that it would be used as data and not as code.

Vulnerable Code:

List results = session.createQuery("from Orders as orders where orders.id = " + currentOrder.getId()).list();

List results = session.createSQLQuery("Select * from Books where author = " + book.getAuthor()).list();

Why this code is vulnerable ?

Ans:

Assuming orderId and author are user input that have not been validated or escaped, it leaves the above queries vulnerable to SQL and HQL(ORM) injection attacks.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-hibernate

3.      Java Persistence API(JPA):

How to Fix SQL Injection using the Java Persistence API (JPA) ?

Java Persistence API (JPA), is an ORM solution that is a part of the Java EE framework. It helps manage relational data in applications that use Java SE and Java EE. It is a common misconception that ORM solutions like JPA (Java Persistence API) are SQL Injection proof. JPA allows the use of native SQL and defines its own query language, named, JPQL (Java Persistence Query Language). The former is prone to traditional SQL injection attacks and the later is prone to JPQL (or ORM) injection attacks.

This article is intended to illustrate how certain syntax offered by JPA to define SQL & HQL, is better over the other, in terms of defense against SQL and/or HQL injection attacks.

Secure usage:

Code-1:

/* positional parameter in JPQL */

Query jpqlQuery = entityManager.createQuery("Select order from Orders order where order.id = ?1");

List results = jpqlQuery.setParameter(1,"123-ADB-567-QTWYTFDL").getResultList();

Code-2:

/* named parameter in JPQL */

Query jpqlQuery = entityManager.createQuery("Select emp from Employees emp where emp.incentive > :incentive");

List results = jpqlQuery.setParameter("incentive",

new Long(10000)).getResultList();

Code-3:

/* named query in JPQL - Query named "myCart" being "Select c from Cart c where c.itemId = :itemId" */

Query jpqlQuery = entityManager.createNamedQuery("myCart");

List results = jpqlQuery.setParameter("itemId", "item-id-0001").getResultList();

Code-4:

/* Native SQL */

Query sqlQuery = entityManager.createNativeQuery("Select * from Books where author = ?", Book.class);

List results = sqlQuery.setParameter(1, "Charles Dickens").getResultList();

Why above 4 codes are secure ?

Ans:

The above code snippets use parameter binding to set data. The JDBC driver will escape this data appropriately before the query is executed; making sure that data is used just as data.

Assuming data used in the above code snippets is user input, that has not been validated or escaped and it contains malicious database code (payload), the payload will be escaped appropriately by the JDBC driver (since parameterized queries are used), such that it would be used as data and not as code.

Vulnerable Code:

List results = entityManager.createQuery("Select order from Orders order where order.id = " + orderId).getResultList();

List results = entityManager.createNativeQuery("Select * from Books where author = " + author).getResultList();

int resultCode = entityManager.createNativeQuery("Delete from Cart where itemId = " + itemId).executeUpdate();

Why this code is vulnerable ?

Ans:

Assuming orderId, author & itemId are user input that have not been validated or escaped as required, it leaves the above queries vulnerable to SQL and JPQL (ORM) injection attacks.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-persistence-api-jpa

You can use Prepared Statements wrong like this:

Code:

String strUserName = request.getParameter("Txt_UserName");
 PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");

So be sure to use Prepared Statements WITH ALL Bind Variables.

Code:

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();