Tuesday, December 29, 2009

Tag checkboxes in Spring MVC


In jsp page with Spring MVC you can use form tags.


With tag checkboxes you can create elegant structure of code in form, for example if you have typical 1:N or M:N relation between model classes.


I show 1:N relation between SECTION and PRODUCT class. It example uses Spring MVC and form-tag checkboxes.


Class Section

Section class is common POJO. Class contains reference to Product class.


public class Section {
...
private List products;
...
}

Class Product

Correct designed method boolean equals:Obj is very important for useful usage checkboxes tag.



public class Product {

private Integer Id;
private String title;

...

@Override
public boolean equals(Object obj) {
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
final Product other = (Product) obj;
if (this.Id != other.Id && (this.Id == null || !this.Id.equals(other.Id))) {
return false;
}
if ((this.title == null) ? (other.title != null) : !this.title.equals(other.title)) {
return false;
}
return true;
}
}

Controller

It is important create Property editor for reference class (Product). Property editor is register in method initBinder. As you see the attribute allProducts in method getAllProducts is automatically putting to model. It is need in view section_edit.jsp.


@ModelAttribute("allProducts")
public List getAllProducts(){
return productDao.all();
}

@RequestMapping(value="/section_edit.htm", method=RequestMethod.GET)
public Section sectionEdit(Integer sectionId){
return sectionDao.findById(sectionId);
}

@RequestMapping(value="/section_edit.htm", method=RequestMethod.POST)
public String sectionEdit(Section section, Errors errors){
...
sectionDao.saveOrUpdate(section);
return "redirect:/sections.htm";
}

@InitBinder
public void initBinder(WebDataBinder binder) {
binder.registerCustomEditor(Product.class, new ProductPropertyEditor());
}

ProductPropertyEditor

In ProductProperty editor I override only method void setAsText:String. This method create Product instance from product.id represented as test in jsp form (section_edit.jsp).


public class ProductPropertyEditor extends PropertyEditorSupport{
...
@Override
public void setAsText(final String text) {
setValue(productDao.findById(Integer.parseInt(text)));
}

}

section_edit.jsp

Expression ${allProducts} read model attribute allProduct setting in controller. It contain List of all products. As value of checkboxes tag attribute itemValue I set id. It use product.id value and this value can read method void setAsText:String from ProductPropertyEditor, when user submit form.

When tag checkboxes iterate items allProducts, then every Product from allProducts List is compare with Product from Section.products List. When they are the same, then will be rendered checkbox with checked attribute.

Checkbox will bee checked when Product from List of Section.products equals Product from List of allProduct.

You must override toString method in reference object (Product). It is very important behaviour of checkboxes tag from Spring checkboxes tag documentation.

question_edit.jsp

Conclusion

Checkbox is checked when:


  • Property is Collection or array and contains the same Object as wrapped Collection.

Sunday, December 20, 2009

GnuPG

GnuPG ( GNU Privacy Guard ) is the GNU project's complete and free implementation of the OpenPGP standard. GnuPG allows to encrypt and sign your data and communication.

PGP use asymmetrics cryptography. It mean, that PGP use two keys.
  • private key
  • public key
Public key use to verification sign of private key. With public key you can also encrypt text. Decryption is possibly only with private key.
After generate your keys you must protect your private key and import your public key to Key server in Internet. Key server is repository of public PGP keys. You can search and take public keys of other people there.
If you want use GnuPG in comfortable way, you use Firefox plug-in FireGPG.

Create keys

$ gpg --gen-key
gpg (GnuPG) 1.4.9; Copyright (C) 2008 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Please select what kind of key you want:
(1) DSA and Elgamal (default)
(2) DSA (sign only)
(5) RSA (sign only)
Your selection? 1
DSA keypair will have 1024 bits.
ELG-E keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048) 2048
Requested keysize is 2048 bits
Please specify how long the key should be valid.
0 = key does not expire
  = key expires in n days
w = key expires in n weeks
m = key expires in n months
y = key expires in n years
Key is valid for? (0) 2y
Key expires at Tue 20 Dec 2011 07:02:09 PM CET
Is this correct? (y/N) y

You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
"Heinrich Heine (Der Dichter) "

Real name: Tomas Jurman
Email address: your@email.com
Comment:
You selected this USER-ID:
"Tomas Jurman "

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
.+++++++++++++++.++++++++++.+++++.++++++++++++++++++++.+++++..+++++++++++++++++++++++++.++++++++++.+++++++++++++++.+++++++++++++++.++++++++++>++++++++++.........+++++
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
+++++..+++++.++++++++++.++++++++++++++++++++.++++++++++.+++++..++++++++++..+++++.+++++...+++++.++++++++++++++++++++++++++++++++++++++++.+++++.+++++....+++++++++++++++>.++++++++++>...+++++...>+++++........................................................................................................................+++++^^^^^^^^^^^
gpg: key 1DA0400B marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   2  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 2u
gpg: next trustdb check due at 2011-12-19
pub   1024D/1DA0400B 2009-12-20 [expires: 2011-12-20]
Key fingerprint = 30DA B2F5 67B6 52D5 CEFE  B8F3 3500 2542 1DA0 400B
uid                  Tomas Jurman 
sub   2048g/8273984F 2009-12-20 [expires: 2011-12-20]

List keys

$ gpg --list-keys
pub   1024D/09CEB475 2009-12-19 [expires: 2011-12-19]
uid                  Tomáš Jurman 
sub   2048g/4288E83B 2009-12-19 [expires: 2011-12-19]
You can see your ID (hexadecimal number after slash - 09CEB475), identity (name and email address), expiration time and other.

Create revocation key

If You want to revoke your keys for reason If has been your keys compromised. (your private key become public known), you must prepare revocation keys.

It is good idea, prepare your revocation keys immediately after generate your keys. Because if you for example by mistake delete your private key, you can not generate your revocation keys. You can prepare revocation keys in advance, save revocation key to file and use in future if it you need.
$ gpg --gen-revoke 4CA09BBE
sec  1024D/4CA09BBE 2009-12-20 tomas jurman 

Create a revocation certificate for this key? (y/N) y
Please select the reason for the revocation:
0 = No reason specified
1 = Key has been compromised
2 = Key is superseded
3 = Key is no longer used
Q = Cancel
(Probably you want to select 1 here)
Your decision? 1
Enter an optional description; end it with an empty line:
> Key has been compromised
> My women bought my private notebook with my private keys.
>
Reason for revocation: Key has been compromised
Key has been compromised
My women bought my private notebook with my private keys.
Is this okay? (y/N) y

You need a passphrase to unlock the secret key for
user: "tomas jurman "
1024-bit DSA key, ID 4CA09BBE, created 2009-12-20

ASCII armored output forced.
Revocation certificate created.

Please move it to a medium which you can hide away; if Mallory gets
access to this certificate he can use it to make your key unusable.
It is smart to print this certificate and store it away, just in case
your media become unreadable.  But have some caution:  The print system of
your machine might store the data and make it available to others!
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: A revocation certificate should follow

iJsEIBECAFsFAksufFxUHQJLZXkgaGFzIGJlZW4gY29tcHJvbWlzZWQKTXkgd29t
ZW4gYm91Z2h0IG15IHByaXZhdGUgbm90ZWJvb2sgd2l0aCBteSBwcml2YXRlIGtl
eXMuAAoJEK4w30tMoJu+5GEAoJ7gDvekN3DHDj/fg97N6Wx3aM1yAKCrW/EBaVnf
A0AzEx5BoJ01raygyg==
=5ln0
-----END PGP PUBLIC KEY BLOCK-----

Last part of statement you copy and paste to file with name for example "revok.key" and retain in secret place.

Revocation keys

$ gpg --import revok.key

Delete keys

$ gpg --delete-secret-keys 1DA0400B
$ gpg --delete-keys 1DA0400B
First you must delete private key and then public key.

Import your public key to Key server

For sending your public key to Key server you must determine your key ID.
$ gpg --send-keys 09CEB475
gpg: sending key 09CEB475 to hkp server keys.gnupg.net

Search public keys in Key server

$ gpg --search-key yourFriend@email.com
gpg: searching for "yourFriend@email.com" from hkp server keys.gnupg.net
(1) Tomáš Jurman 
1024 bit DSA key 05CEA475, created: 2009-12-19
Keys 1-1 of 1 for "yourFriend@email.com".  Enter number(s), N)ext, or Q)uit > 1
gpg: requesting key 05CEA475 from hkp server keys.gnupg.net
gpgkeys: key 05CEA475 not found on keyserver
gpg: no valid OpenPGP data found.
gpg: Total number processed: 0
The key will bee find and GPG offer you to import key in your keys store.

Export public key to file

$ gpg -a --export your@email.com > my.key

Export secret key to file

gpg --export-secret-keys -a [id] > /home/tomas/mySecrKey.txt
Value Id is optional. If you dont use id, gpg export all yours secret keys

Import public key from file

$ gpg --import my.key

Encrypt file

$ gpg -ear 09CEB475 sourcerPlainText.txt
-e = encryt, -r = recipient, -o = output, -a = armor (to ascii)

Decrypt file

$ gpg -d encryptOutputFile.txt

Encrypt and Sign

gpg -esr 09CEB475 -u 16582121 sourcerPlainText.txt
This create .pgp file decript of recipient with ID 09CEB475 and sign of user with ID 16582121
-e = encrypt; -s - sign; -r = recipient; -u = user

Sign

  • $ gpg -s sourcerPlainText.txt
    Create .pgp file. Use compression and Contain source file data.
  • gpg --clearsign sourcerPlainText.txt
    Create .asc file. Dont use compression. Contain source file data.
  • gpg -b sourcerPlainText.txt
    Create .asc file. Create only sign, not contains data from file.
optional: -a = armor (to ascii); -u = user
gpg -bau 16582121 sourcerPlainText.txt

Fingerprint

gpg --fingerprint [ID | email]


Verify sign

You need 2 files. First file is Sign and second file contains some data for verify.
gpg --verify test.txt.sig test.txt

Create encrypt and sign file for SIMS

Every quarter of year we have to send encrypt and sign file to SIMS.
As -r (recipient) use ID or email address of Matrika studentu - sber dat
As -u (user) use your ID or your email address.
Don´t forget use switch --pgp6.
  1. gpg -easr sims@ics.muni.cz -u yourSchool@emailAddress.cz --pgp6 -o sims.pgp sims.xml
  2. gpg -easr 8E4E1C1C -u 16582121 --pgp6 -o sims.pgp sims.xml
-e = encrypt; -s - sign; -r = recipient; -u = user; -o = output, -a = armor (to ascii)

Monday, November 2, 2009

Quick login with SSH and SSHMenu

Often I use ssh for remote servers control. I must remember a lots of password. I decided solve the problem. For login to servers I will use client certificate and applet SSHmenu.


First I generate the certificate in my personal notebook:

/usr/bin/ssh-keygen -t rsa

Generating public/private rsa key pair.
Enter file in which to save the key (/home/tomas/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/tomas/.ssh/id_rsa.
Your public key has been saved in /home/tomas/.ssh/id_rsa.pub.
The key fingerprint is:
e0:40:a5:2b:b9:ba:24:ae:a3:bb:fe:30:9a:81:30:c6 tomas@homer
The key's randomart image is:
+--[ RSA 2048]----+
| ... |
| . . |
| o . |
|. . + . |
|oEo . . S |
|+. o |
|++. |
|*+o |
|&B.. |
+-----------------+

For question: Enter file in which to save the key I push enter and for question:Enter passphrase I push enter too. I want certificate out of passphrase, but it is not recommended using a private key without a passphrase. (see comments of grantmclean below article)

In folder /home/tomas/.ssh I find files.

-rw------- 1 tomas tomas 1675 2009-11-02 17:43 id_rsa
-rw-r--r-- 1 tomas tomas 392 2009-11-02 17:43 id_rsa.pub

You notice files permission and owner. It is important.


Now is neccessary copy public certificate(id_rsa.pub) from personal notebook to server. After advice of grantmclean (in comments) I use for copy command ssh-copy-id

/usr/bin/ssh-copy-id jurman@akela.cz

Now I set values for ssh daemon in server in file /etc/ssh/sshd.config. In config file I rewrite following rows:

# allow used public rsa certificate (only for protocol 2)
PubkeyAuthentication yes

# deny root login
PermitRootLogin no

# deny password authentication
PasswordAuthentication no

I restart ssh daemon and try login from my notebook to server:

ssh jurman@akela.cz

If I don´t fill the passphrase then I must not write password. Now I am login to server.

Now I can copy the public certificate in other servers too. Your private certificate(id_rsa) You must protect very well. (see comments of grantmclean below article)

In my notebook I install this applet SSHmenu. After setting this applet I can login to server from my notebook in one click.

Thursday, October 29, 2009

Basic firewall on Linux server

As firewall I use netfilter. I show basic firewall rules for connecting server on Internet and sharing connecting wit other PCs in local network. In server usually run a lots of services but I want to public only some of them. PC in local network (LAN) use server for sharing Internet connection. Local PCs can send their packets through server (FORWARDING). Private IP addresses of local PCs are hide behind public IP address (MASQUERADE).

I want to write following rule:

  • For WAN (Internet) allow only Apache (http), Postfix (smtp), and ssh.
  • For LAN (local user) allow use server for sharing Internet connection.

Server has two interfaces.

  • eth0 with public IP connected to Internet
  • eth1 with private IP connected to local network (LAN)

For showing ip information I use command ifconfig.

# /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 00:16:36:88:3E:CC
inet addr:74.125.45.100 Bcast:74.125.45.255 Mask:255.255.255.0
inet6 addr: fe80::216:36ff:fe88:3ecc/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:47362 errors:0 dropped:0 overruns:0 frame:0
TX packets:47094 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:18821515 (17.9 MiB) TX bytes:30314950 (28.9 MiB)
Interrupt:74

eth1 Link encap:Ethernet HWaddr 00:16:36:88:3E:CD
inet addr:10.10.10.1 Bcast:10.10.10.255 Mask:255.255.255.0
inet6 addr: fe80::216:36ff:fe88:3ecd/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:23493 errors:0 dropped:0 overruns:0 frame:0
TX packets:23511 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:4045104 (3.8 MiB) TX bytes:15666040 (14.9 MiB)
Interrupt:82

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:27071 errors:0 dropped:0 overruns:0 frame:0
TX packets:27071 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:23502830 (22.4 MiB) TX bytes:23502830 (22.4 MiB)

Now I can write following script.

#!/bin/sh
##firewall script
#chkconfig: 2345 01 99

#----------------------------------------------------------------------------#
# Define variables
#----------------------------------------------------------------------------#

IPTABLES="/sbin/iptables"
LAN_IFACE="eth1"
WAN_IFACE="eth0"

#----------------------------------------------------------------------------#
# Inicialization
#----------------------------------------------------------------------------#

#Flush all rules, delete all chains
$IPTABLES -F
$IPTABLES -X
$IPTABLES -t nat -F
$IPTABLES -t nat -X
$IPTABLES -t mangle -F
$IPTABLES -t mangle -X

#Zero out all counters
$IPTABLES -Z
$IPTABLES -t nat -Z
$IPTABLES -t mangle -Z

#----------------------------------------------------------------------------#
# Set default policies
#----------------------------------------------------------------------------#

$IPTABLES -P INPUT DROP
$IPTABLES -P FORWARD DROP
$IPTABLES -P OUTPUT ACCEPT
$IPTABLES -t nat -P OUTPUT ACCEPT
$IPTABLES -t nat -P PREROUTING ACCEPT
$IPTABLES -t nat -P POSTROUTING ACCEPT
$IPTABLES -t mangle -P PREROUTING ACCEPT
$IPTABLES -t mangle -P POSTROUTING ACCEPT

#----------------------------------------------------------------------------#
# INPUT
#----------------------------------------------------------------------------#

#these lines are necessary for the loopback interface and internal socket-based services to work correctly
$IPTABLES -A INPUT -i lo -j ACCEPT

#allow all from LAN
$IPTABLES -A INPUT -i $LAN_IFACE -j ACCEPT

#allow answer packets from Internet
$IPTABLES -A INPUT -i eth0 -m state --state ESTABLISHED,RELATED -j ACCEPT

#accept important ICMP packets
$IPTABLES -A INPUT -p icmp --icmp-type echo-request -j ACCEPT
$IPTABLES -A INPUT -p icmp --icmp-type time-exceeded -j ACCEPT
$IPTABLES -A INPUT -p icmp --icmp-type destination-unreachable -j ACCEPT

#allow services
$IPTABLES -A INPUT -p tcp --dport 22 --sport 1024:65535 -j ACCEPT # SSH
$IPTABLES -A INPUT -p tcp --dport 80 --sport 1024:65535 -j ACCEPT # HTTP
$IPTABLES -A INPUT -p tcp --dport 25 --sport 1024:65535 -j ACCEPT # SMTP

#----------------------------------------------------------------------------#
# OUTPUT
#----------------------------------------------------------------------------#

# all allow

$IPTABLES -t nat -A POSTROUTING -o $WAN_IFACE -j MASQUERADE

#----------------------------------------------------------------------------#
# FORWARD
#----------------------------------------------------------------------------#

$IPTABLES -A FORWARD -i $WAN_IFACE -o $LAN_IFACE -m state --state ESTABLISHED,RELATED -j ACCEPT
$IPTABLES -A FORWARD -i $LAN_IFACE -o $WAN_IFACE -m state --state NEW,ESTABLISHED,RELATED -j ACCEPT

#----------------------------------------------------------------------------#
# End
#----------------------------------------------------------------------------#

# Save settings
/sbin/service iptables save

# List rules
$IPTABLES -L -v -n

For forwarding is neccessary set variable net.ipv4.ip_forward=1. The best way is find file /etc/sysctl.conf and rewrite the value net.ipv4.ip_forward=0 to net.ipv4.ip_forward=1. Then I must save the changes in file. I use command:

/sbin/sysctl -p

After run script in server and test firewall from PC in local network, You can test firewall from other PC from Internet:

# /usr/bin/nmap 74.125.45.100

When is all right You can adapt this script or add your specific rules.

Sunday, October 25, 2009

Mysql - join multiple rows

This week I need fill one table from data from another tables. It was necessary join multiple columns to one column and join multiple rows to one row too.

I use function CONCAT and GROUP_CONCAT in Mysql

I create table RESULT_TABLE for result data. The table contains any data. The table look like:


mysql> select * from RESULT_TABLE;
+---------+------------+-------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+-------------+------------+
| meanwhile EMPTY |
+---------+------------+-------------+------------+

In DB I have following table:


mysql> select * from BOOK;
+---------+------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_SCOPE |
+---------+------------+------------+
| 1 | title 1 | law |
| 2 | title 2 | law |
| 3 | title 3 | math |
+---------+------------+------------+

mysql> select * from AUTHOR;
+-----------+------------------+-----------------+
| AUTHOR_ID | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME |
+-----------+------------------+-----------------+
| 1 | first1 | last1 |
| 2 | first2 | last2 |
| 3 | first3 | last3 |
| 4 | first4 | last4 |
| 5 | first5 | last5 |
+-----------+------------------+-----------------+

mysql> select * from BOOK_X_AUTHOR;
+---------+-----------+
| BOOK_ID | AUTHOR_ID |
+---------+-----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
+---------+-----------+


In first step I join tables


mysql> select BOOK.BOOK_ID, BOOK.BOOK_TITLE, AUTHOR.AUTHOR_FIRSTNAME, AUTHOR.AUTHOR_LASTNAME, BOOK.BOOK_SCOPE from BOOK, AUTHOR, BOOK_X_AUTHOR
-> where
-> BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID and
-> BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+------------------+-----------------+------------+
| BOOK_ID | BOOK_TITLE | AUTHOR_FIRSTNAME | AUTHOR_LASTNAME | BOOK_SCOPE |
+---------+------------+------------------+-----------------+------------+
| 1 | title 1 | first1 | last1 | law |
| 1 | title 1 | first2 | last2 | law |
| 2 | title 2 | first3 | last3 | law |
| 2 | title 2 | first4 | last4 | law |
| 3 | title 3 | first5 | last5 | math |
+---------+------------+------------------+-----------------+------------+


In second step I use function concat and alias. Function concat return more columns as one column. With alias we can return another column name (BOOK_AUTHOR).


mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
-> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
-> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
-> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID;
+---------+------------+--------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+--------------+------------+
| 1 | title 1 | first1 last1 | law |
| 1 | title 1 | first2 last2 | law |
| 2 | title 2 | first3 last3 | law |
| 2 | title 2 | first4 last4 | law |
| 3 | title 3 | first5 last5 | math |
+---------+------------+--------------+------------+


In third step I use function GROUP_CONCAT. You notice the clause GROUP BY at the end of select. I join multiple rows from table AUTHOR to one colum.


mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat(concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
-> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
-> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
-> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
-> group by BOOK_ID;
+---------+------------+---------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+---------------------------+------------+
| 1 | title 1 | first2 last2,first1 last1 | law |
| 2 | title 2 | first4 last4,first3 last3 | law |
| 3 | title 3 | first5 last5 | math |
+---------+------------+---------------------------+------------+


I add SEPARATOR and clause ORDER BY to function GROUP_CONCAT


mysql> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
-> ORDER BY AUTHOR.AUTHOR_ID
-> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
-> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
-> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
-> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
-> GROUP BY BOOK_ID;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
| 1 | title 1 | first1 last1, first2 last2 | law |
| 2 | title 2 | first3 last3, first4 last4 | law |
| 3 | title 3 | first5 last5 | math |
+---------+------------+----------------------------+------------+


At the end I inserting result to RESULT_TABLE


mysql> insert into RESULT_TABLE (BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, BOOK_SCOPE)
-> SELECT BOOK.BOOK_ID, BOOK.BOOK_TITLE, group_concat( concat( AUTHOR.AUTHOR_FIRSTNAME, " ", AUTHOR.AUTHOR_LASTNAME )
-> ORDER BY AUTHOR.AUTHOR_ID
-> SEPARATOR ", " ) AS BOOK_AUTHOR, BOOK.BOOK_SCOPE
-> FROM BOOK, AUTHOR, BOOK_X_AUTHOR
-> WHERE BOOK.BOOK_ID = BOOK_X_AUTHOR.BOOK_ID
-> AND BOOK_X_AUTHOR.AUTHOR_ID = AUTHOR.AUTHOR_ID
-> GROUP BY BOOK_ID;

mysql> select * from RESULT_TABLE;
+---------+------------+----------------------------+------------+
| BOOK_ID | BOOK_TITLE | BOOK_AUTHOR | BOOK_SCOPE |
+---------+------------+----------------------------+------------+
| 1 | title 1 | first1 last1, first2 last2 | law |
| 2 | title 2 | first3 last3, first4 last4 | law |
| 3 | title 3 | first5 last5 | math |
+---------+------------+----------------------------+------------+