Source code

001/**
002 * Copyright 2014 Tampere University of Technology, Pori Department
003 * 
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * 
008 *   http://www.apache.org/licenses/LICENSE-2.0
009 * 
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package service.tut.pori.users.facebook;
017
018import java.util.Date;
019import java.util.Map;
020import java.util.Map.Entry;
021
022import org.apache.commons.lang3.StringUtils;
023import org.apache.log4j.Logger;
024import org.springframework.beans.factory.annotation.Autowired;
025import org.springframework.jdbc.core.JdbcTemplate;
026import org.springframework.transaction.TransactionStatus;
027import org.springframework.transaction.support.TransactionCallback;
028
029import service.tut.pori.users.google.OAuth2Token;
030import service.tut.pori.users.UserDAO;
031import core.tut.pori.dao.SQLDAO;
032import core.tut.pori.dao.clause.SQLClause.SQLType;
033import core.tut.pori.users.ExternalAccountConnection;
034import core.tut.pori.users.UserIdentity;
035import core.tut.pori.users.ExternalAccountConnection.UserServiceType;
036import core.tut.pori.utils.NonceUtils;
037import core.tut.pori.utils.NonceUtils.Nonce;
038
039/**
040 * DAO for handling authorization tokens for Facebook User Service.
041 *
042 */
043public class FacebookUserDAO extends SQLDAO {
044  private static final Logger LOGGER = Logger.getLogger(FacebookUserDAO.class);
045  /* tables */
046  private static final String TABLE_USERS_FACEBOOK = DATABASE+".users_facebook";
047  /* columns */
048  private static final String COLUMN_ACCESS_TOKEN = "access_token";
049  private static final String COLUMN_ACCESS_TOKEN_EXPIRES = "access_token_expires";
050  private static final String COLUMN_NONCE = "nonce";
051  private static final String COLUMN_NONCE_EXPIRES = "nonce_expires";
052  /* sql strings */ 
053  private static final String SQL_COUNT_USER_ID = "SELECT "+COLUMN_COUNT+" FROM "+TABLE_USERS_FACEBOOK+" WHERE "+COLUMN_USER_ID+"=?";
054  private static final int[] SQL_COUNT_USER_ID_SQL_TYPES = new int[]{SQLType.LONG.toInt()};
055
056  private static final String SQL_INSERT_NONCE = "INSERT INTO "+TABLE_USERS_FACEBOOK+" ("+COLUMN_USER_ID+","+COLUMN_NONCE+","+COLUMN_NONCE_EXPIRES+","+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW())";
057  private static final int[] SQL_INSERT_NONCE_SQL_TYPES = new int[]{SQLType.LONG.toInt(),SQLType.STRING.toInt(),SQLType.LONG.toInt()};
058
059  private static final String SQL_REMOVE_NONCE = "UPDATE "+TABLE_USERS_FACEBOOK+" SET "+COLUMN_NONCE+"=NULL WHERE "+COLUMN_NONCE+"=? LIMIT 1";
060  private static final int[] SQL_REMOVE_NONCE_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
061  
062  private static final String SQL_REMOVE_TOKEN = "DELETE FROM "+TABLE_USERS_FACEBOOK+" WHERE "+COLUMN_USER_ID+"=?";
063  private static final int[] SQL_REMOVE_TOKEN_SQL_TYPES = new int[]{SQLType.LONG.toInt()};
064  
065  private static final String SQL_SELECT_TOKEN = "SELECT "+COLUMN_COUNT+", "+COLUMN_ACCESS_TOKEN+", "+COLUMN_ACCESS_TOKEN_EXPIRES+" FROM "+TABLE_USERS_FACEBOOK+" WHERE "+COLUMN_USER_ID+"=? AND "+COLUMN_ACCESS_TOKEN_EXPIRES+">? LIMIT 1";
066  private static final int[] SQL_SELECT_TOKEN_SQL_TYPES = new int[]{SQLType.LONG.toInt(),SQLType.LONG.toInt()};
067  
068  private static final String SQL_SELECT_USER_ID = "SELECT "+COLUMN_COUNT+", "+COLUMN_USER_ID+" FROM "+TABLE_USERS_FACEBOOK+" WHERE "+COLUMN_NONCE+"=? AND "+COLUMN_NONCE_EXPIRES+">? LIMIT 1";
069  private static final int[] SQL_SELECT_USER_ID_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.LONG.toInt()};
070
071  /** tokens generated by facebook should be unique, so the unique column constraint on access token should never match on ON DUPLICATE KEY UPDATE for two different users, the match will be solely done on user id */
072  private static final String SQL_SET_TOKEN = "INSERT INTO "+TABLE_USERS_FACEBOOK+" ("+COLUMN_USER_ID+","+COLUMN_ACCESS_TOKEN+","+COLUMN_ACCESS_TOKEN_EXPIRES+","+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_ACCESS_TOKEN+"=VALUES("+COLUMN_ACCESS_TOKEN+"), "+COLUMN_ACCESS_TOKEN_EXPIRES+"=VALUES("+COLUMN_ACCESS_TOKEN_EXPIRES+")";
073  private static final int[] SQL_SET_TOKEN_SQL_TYPES = new int[]{SQLType.LONG.toInt(),SQLType.STRING.toInt(),SQLType.LONG.toInt()};
074  
075  private static final String SQL_UPDATE_NONCE = "UPDATE "+TABLE_USERS_FACEBOOK+" SET "+COLUMN_NONCE+"=?,"+COLUMN_NONCE_EXPIRES+"=? WHERE "+COLUMN_USER_ID+"=?";
076  private static final int[] SQL_UPDATE_NONCE_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.LONG.toInt(),SQLType.LONG.toInt()};
077  
078  @Autowired
079  private UserDAO _userDAO = null;
080  
081  /**
082   * 
083   * @param facebookUserId
084   * @param token
085   * @param userId
086   * @return true on success
087   */
088  public boolean setToken(final String facebookUserId, final OAuth2Token token, final UserIdentity userId){
089    if(!UserIdentity.isValid(userId) || !token.isValid() || StringUtils.isBlank(facebookUserId)){
090      LOGGER.debug("Invalid userId or token.");
091      return false;
092    }
093    
094    return getTransactionTemplate().execute(new TransactionCallback<Boolean>() {
095
096      @Override
097      public Boolean doInTransaction(TransactionStatus status) {      
098        JdbcTemplate t = getJdbcTemplate();
099        
100        String oldFacebookUserId = getFacebookUserId(userId);
101        if(oldFacebookUserId == null){
102          LOGGER.debug("Creating a new external account connection for user, id: "+userId.getUserId()+", google user id: "+facebookUserId);
103          _userDAO.insertExternalAccountConnection(new ExternalAccountConnection(facebookUserId, UserServiceType.FACEBOOK), userId);
104        }else if(!oldFacebookUserId.equals(facebookUserId)){ // do not allow changing the facebook user, this may cause undefined behavior on other parts of the system
105          LOGGER.warn("The given facebook user Id "+facebookUserId+" did not match the old facebook user id: "+oldFacebookUserId);
106          return false;
107        }
108        
109        t.update(SQL_SET_TOKEN, new Object[]{userId.getUserId(),token.getAccessToken(),token.getExpires().getTime()}, SQL_SET_TOKEN_SQL_TYPES);
110        
111        return true;
112      }
113    });
114  }
115  
116  /**
117   * 
118   * @param userId non-null, valid userid
119   * @return facebook user id for the given userId, if known by the system, otherwise returns null
120   */
121  public String getFacebookUserId(UserIdentity userId){
122    ExternalAccountConnection connection = _userDAO.getExternalAccountConnection(userId, UserServiceType.FACEBOOK);
123    return (connection == null ? null : connection.getExternalId());
124  }
125  
126  /**
127   * 
128   * @param nonce
129   */
130  public void removeNonce(String nonce){
131    if(getJdbcTemplate().update(SQL_REMOVE_NONCE, new Object[]{nonce}, SQL_REMOVE_NONCE_SQL_TYPES) < 1){
132      LOGGER.debug("No nonce was cleared.");
133    }else{
134      LOGGER.debug("Nonce cleared.");
135    }
136  }
137  
138  /**
139   * 
140   * @param userId
141   * @return token for the given user or null if none available
142   */
143  public OAuth2Token getToken(UserIdentity userId){
144    if(!UserIdentity.isValid(userId)){
145      LOGGER.debug("Invalid userId.");
146      return null;
147    }
148    
149    OAuth2Token token = new OAuth2Token();
150    Map<String, Object> row = getJdbcTemplate().queryForMap(SQL_SELECT_TOKEN, new Object[]{userId.getUserId(), System.currentTimeMillis()}, SQL_SELECT_TOKEN_SQL_TYPES);
151    for(Entry<String,Object> entry : row.entrySet()){
152      switch(entry.getKey()){
153        case COLUMN_ACCESS_TOKEN:
154          token.setAccessToken((String) entry.getValue());
155          break;
156        case COLUMN_ACCESS_TOKEN_EXPIRES:
157          token.setExpires(new Date((Long) entry.getValue()));
158          break;
159        default:
160          if(checkCountColumn(entry.getKey(), entry.getValue()) < 1){ // if we have only one column, check what it is (should be count)
161            LOGGER.debug("Unknown column name, or no results.");
162            return null;
163          }
164          break;
165      }
166    }
167    return token;
168  }
169  
170  /**
171   * This generates a new nonce from the given UserId, replacing the old one if one was present
172   * 
173   * @param userId
174   * @return randomly generated nonce for the given user
175   */
176  public String generateNonce(UserIdentity userId){
177    if(!UserIdentity.isValid(userId)){
178      LOGGER.debug("Invalid userId.");
179      return null;
180    }
181    Nonce nonce = NonceUtils.generateNonce(); // in theory, it is extremely unlikely that the generated nonce will collide with previously generated one, but...
182    if(recordExists(userId.getUserId())){ // ...just in case do not use INSERT ... ON DUPLICATE KEY UPDATE for updating the nonce (as nonce itself is an unique column)
183      getJdbcTemplate().update(SQL_UPDATE_NONCE, new Object[]{nonce.getNonce(),nonce.getExpires(),userId.getUserId()}, SQL_UPDATE_NONCE_SQL_TYPES); // this throw an exception on duplicate nonce if one ever occurs
184    }else{
185      getJdbcTemplate().update(SQL_INSERT_NONCE, new Object[]{userId.getUserId(),nonce.getNonce(),nonce.getExpires()}, SQL_INSERT_NONCE_SQL_TYPES); // this throw an exception on duplicate nonce if one ever occurs
186    }
187    return nonce.getNonce();
188  }
189  
190  /**
191   * remove token from the given userIdentity if one exists
192   * 
193   * @param userId
194   */
195  public void removeToken(UserIdentity userId){
196    if(!UserIdentity.isValid(userId)){
197      LOGGER.debug("Invalid userId.");
198      return;
199    }
200    Long id = userId.getUserId();
201    if(getJdbcTemplate().update(SQL_REMOVE_TOKEN,new Object[]{id},SQL_REMOVE_TOKEN_SQL_TYPES) < 1){
202      LOGGER.debug("No tokens removed for userId: "+id);
203    }else{
204      LOGGER.debug("Token removed for userId: "+id);
205    }
206  }
207  
208  /**
209   * Note: this will not populate the UserIdentity, only userId will be set
210   * 
211   * @param nonce
212   * @return owner of the given nonce, or null if no such nonce exists or the nonce has expired
213   */
214  public UserIdentity getUser(String nonce){
215    Long userId = (Long) getJdbcTemplate().queryForMap(SQL_SELECT_USER_ID, new Object[]{nonce,System.currentTimeMillis()}, SQL_SELECT_USER_ID_SQL_TYPES).get(COLUMN_USER_ID);
216    return (userId == null ? null : new UserIdentity(userId));
217  }
218  
219  /**
220   * 
221   * @param userId
222   * @return true if record exists for the given user
223   */
224  private boolean recordExists(Long userId){
225    return (getJdbcTemplate().queryForObject(SQL_COUNT_USER_ID, new Object[]{userId}, SQL_COUNT_USER_ID_SQL_TYPES, Long.class) > 0);
226  }
227}