Security Issue for Java and MySQL or SQL

The most basic vulnerability is to leave the database operating on the default port of 3306 with the default user name and password. Ensure that this port is not open to the web.

SQL Injection Vulnerability

To protect against SQL Injection, it is necessary to avoid methods which take an argumenrt which is directly used in an SQL query.

The solution is a two setp process.

  1. First, validate the input argument against a regex or caught parse exception. Therefore you will know, if the method is supposed to accept a String containing a int, or an entire set of digits, you can be sure that this is the case. This ensures you are not using user input to directly query the database without validating it first.
  2. The second prevention step is not to use a direct SQL query. Instead use a prepared statement. This means that you are not using string concatenation or string replacement to query the database. If this were the case, an attacker can pass your method an SQL statement which will then be run against your database.

Auto Incrementing an SQL column which doesnt have the AUTO INCREMENT constraint

You need to use a sequence. You can see the available sequences in a schema in Squirrel SQL. These sequences can be auto generated or manually created. A sequence is generated automatically when you make a column on any table auto increment. This is the case for the 'SYSTEM_SEQUENCE_XX..' below. The other sequences were manually written.


sql-sequence-screenshot

The list of sequences seen below the tables

The syntax for inserting into a table which has an id whcih cant be null and needs to increment, but which doesnt have an auto increment constraint, is below:

INSERT INTO AUTHORITY(order_id, foo1, foo2, foo3)SELECT AUTHORITY_PK_SEQ.nextval,foo1, foo2, foo3
                                FROM bar
                                WHERE X = Y

SQL Join Two Result Sets / Join Two Select Statement Results into one Result Set

Thanks to Mark Byers on Stack Overflow.


SELECT T1.col_a, T1.col_b, T2.col_c
FROM(SELECT col_a, col_b,...etc...)AS T1
JOIN(SELECT col_a, col_c,...etc...)AS T2
ON T1.col_a = T2.col_a

Running SQL file directly from command line

To run your SQL script directly from the command line:

 

1 – FTP your SQL file up to the server / machine / host

2 – cd to the SQL file location.

3 – to run the file type:

sql databaseName < yourscriptname.sql> yourScript.log

example:

sql my_database <load_table.sql> loading_log.log

Load SQL table from text file Linux command line

Recently I was required to read in a text file into an SQL database table. The pre-requisite was that the fields of the file were an even width, ie, each column was 50 characters wide even if the text filling it was less than 50 characters.

To run the below script, I ftp’d it to a directory on a linux machine. The sql database was on the same machine.

First I wrote the housekeeping function. This set up logs and took care of general good housekeeping practices.

Then I had a function create the table I required.

Then I had a function read from the text file (which just happened to be saved with a .OUT file extension.  Of course, the file which you want to read from should be in the directory as specified in the housekeeing function.

Below are the contents of the shell script. Of course, to run the script the command on linux command line was

./script_name.sh

 

 

 

housekeeping() {

work=/vectorwise/rawdata/dNiall log=$work/mydirectory/log data=$work/mydirectory/data tmp=$work/mydirectory/tmp my_database=xynt_db my_database_logfile=$log/mydirectory/my_database_sql.log FILE_DELIMITER=$

if [ ! -d $work ]; then      mkdir $work fi

if [ ! -d $log ]; then      mkdir $log fi

if [ ! -d $data ]; then      mkdir $data fi

if [ ! -d $tmp ]; then      mkdir $tmp fi

}

create_table() {

sql $my_database << !! > $my_database_logfile

drop table people \p\g

create table people (         type char(2),         name char(50),         addr1 char(50),         addr2 char(50),         addr3  char(50),         addr4 char(50) )

\p\g

CALL VECTORWISE( COMBINE ‘people-people’) \p\g !! } load_table_from_file() { sql $my_database << !! > $my_database_logfile copy people (         type = char(2),         name char(50),         addr1 char(50),         addr2 char(50),         addr3  char(50),         addr4 char(50)         nl ) from ‘$work/files/PEOPLE_FILE.OUT’ \p\g !! } #below calls the functions in order housekeeping create_table load_table_from_file

SQL select varchar substring from middle of varchar

I recently had to pull out a substring from a larger string in SQL.  The problem was that I required 7 digits from an 20 digit varchar value.

So, the id field contained:

1928P746781S3390W563

and I wanted the 7 digits from the middle of the varchar as highlighted in bold below:

1928P746781S3390W563.

 

The following SQL statement achieved this for me:

SELECT substring(id from 11 for 7) as new_id from persons.

This left me with a value of S3390W5.

SQL Update Table 1 from Table 2

In the following example, I wanted to update a persons id in table 1 to the value of the same persons id in table 2.

 

UPDATE table_1

SET id =

 ( SELECT table_2.id

FROM table_2

WHERE table_1.id = table_2.id)