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?

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Question:

I want to run the query: "DESCRIBE table_name;"

statement = this.connection.createStatement();
ResultSet rset = statement.executeQuery("DESCRIBE table_name");

and I got this error:

 " java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement"

what is the problem?

Answer:

describe user2.flights;

Here user2 is database name and flights is table name. Try this.

Or use next query

select *
  from user_tab_columns
 where table_name = 'MY_TABLE'
 order by column_id;  

Use this query.

column_id is the "order" of the column in the table.

You should ensure that 'MY_TABLE' is capitalized unless you've been adding tables with casing ( a bad idea ) in which case you need to use something like = "MyTable"

Original Link: Run Oracle query from Java

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

Oracle to PostgreSQL Conversion, Sequence Update Query Details

1. Oracle to PostgreSQL conversion
2. Updating Sequences Automatically in Postgresql
3. Updating sequence values from table
4. How to reset postgres' primary key sequence when it falls out of sync?

Others:

1. Sequence Manipulation Functions