EntityManager creates its own request against save and injection

Question

Is createNativeQuery() safe for SQL injection if used as in:

 @ManagedBean @ViewScoped public class UserController { @PersistenceContext private EntityManager em; public User register(User u) { Query query = em.createNativeQuery("SELECT r1_register(?,?,?,?,?,?,?)"); short i = 0; query.setParameter(++i, u.getUsername()); query.setParameter(++i, u.getPassword()); query.setParameter(++i, u.getName()); query.setParameter(++i, u.getSurname()); query.setParameter(++i, u.getEmail()); query.setParameter(++i, u.getBirthdate()); query.setParameter(++i, u.getPhoneNumber()); int id = (int) query.getSingleResult(); if (id != 0) u.setIduser(id); return u; } } 

r1_register is a stored function that executes an INSERT and returns the identifier of the newly inserted user. This will be equivalent to:

 public User register(User u) { em.persist(u); // get the last inserted id (user id must be @Generated) em.flush(); // user id set here return u; } 

u in both cases filled by the user. Finally, the default transaction?

EDIT: procedure:

 CREATE DEFINER=`root`@`localhost` FUNCTION `r1_register`(username VARCHAR(45), _password VARCHAR(45), _name VARCHAR(45), surname VARCHAR(45), _email VARCHAR(45), _birthdate DATE, phone_number VARCHAR(10) ) RETURNS int(11) BEGIN -- Adds a new user. -- START TRANSACTION; -- Begin a transaction -- NOT ALLOWED -- http://stackoverflow.com/questions/16969875/ IF r1_check_unique_username(username)=0 THEN RETURN 0; END IF; INSERT IGNORE INTO `hw1_db`.`users` (`username`, `password`, `name`, `surname`, `email`, `birthdate`, `phone_number`) VALUES (username, _password, _name, surname, _email, _birthdate, phone_number); -- see: http://stackoverflow.com/a/5939840/281545 -- The drawback to this approach is that you cannot go back and use -- ids wasted because of failed attempts to INSERT IGNORE in the event -- of a duplicate key. Shouldn't be a problem for us as we check. -- /Transaction -- IF ROW_COUNT() > 0 THEN -- ROW_COUNT() returns the number of rows updated/inserted/deleted -- COMMIT; -- Finalize the transaction -- ELSE -- ROLLBACK; -- Revert all changes made before the transaction began -- END IF; RETURN LAST_INSERT_ID(); END 

Answer 1

It depends on what r1_register really does. If it just saves the user and nothing more than yes, they are equivalent, because that is what EntityManager#persist does. But if the database function does some security checks or writes to other tables, you also need to implement it in JPA. The btw code to insert User and get the identifier must be

 public User register(User u) { em.getTransaction().begin(); em.persist(u); em.getTransaction().commit(); int id = u.getId(); return u; } 

But you do not need to call EntityManager#flush , if you need this identifier after calling the register method, flush is executed at the end of each transaction.