func CreateAlert(db *sql.DB, user int, category string, targetId int) error { stmt, err := db.Prepare(` INSERT INTO alerts (user, category, target_id) SELECT ? AS user, ? AS category, ? AS target_id FROM dual WHERE NOT EXISTS( SELECT user FROM alerts WHERE user = ? AND category = ? AND target_id = ? ) LIMIT 1; `) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() _, err = stmt.Exec(user, category, targetId, user, category, targetId) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } err = emailAlert(db, user, category, targetId) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } return nil }
func returnAlertMessage(db *sql.DB, recipient int, sender int) (AlertMessage, error) { result := AlertMessage{} stmt, err := db.Prepare(` SELECT u.name, u.custom_picture, m.message FROM messages AS m JOIN users AS u ON m.sender = u.id WHERE m.receiver = ? AND m.sender = ? ORDER BY m.date DESC LIMIT 1; `) if err != nil { return AlertMessage{}, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() customPicture := false err = stmt.QueryRow(recipient, sender).Scan(&result.Name, &customPicture, &result.Message) if err != nil { return AlertMessage{}, util.NewError(nil, "Mensaje no existe", 400) } if customPicture { result.Picture = "https://5sur.com/images/" + result.Name + "_35.png" } else { result.Picture = "https://5sur.com/default_35.png" } return result, nil }
func DeleteAlert(db *sql.DB, user int, category string, targetId int) error { stmt, err := db.Prepare(` DELETE FROM alerts WHERE user = ? AND category = ? AND ( ( category = "removed" OR category = "deleted" ) OR target_id = ? ); `) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() _, err = stmt.Exec(user, category, targetId) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } return nil }
func GetAlerts(db *sql.DB, user int) ([]template.HTML, error) { var results []template.HTML stmt, err := db.Prepare(` SELECT category, target_id FROM alerts WHERE user = ?; `) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() rows, err := stmt.Query(user) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } defer rows.Close() for rows.Next() { var category string var targetId int err := rows.Scan(&category, &targetId) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } content, err := createAlertContent(db, user, category, targetId) if err != nil { // Log this probably } else { results = append(results, template.HTML(content)) } } return results, nil }
func deleteFromReservations(db *sql.DB, driverId int, listingId int, passengerId int) (bool, error) { stmt, err := db.Prepare(` DELETE FROM reservations WHERE driver_id = ? AND listing_id = ? AND passenger_id = ? `) if err != nil { return false, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() affected, err := stmt.Exec(driverId, listingId, passengerId) if err != nil { return false, util.NewError(err, "Error de la base de datos", 500) } rowsDeleted, err := affected.RowsAffected() if err != nil { return false, util.NewError(err, "Error de la base de datos", 500) } if rowsDeleted == 0 { return false, nil } return true, nil }
func ReturnUserPicture(db *sql.DB, user int, size string) (string, error) { picture := "" stmt, err := db.Prepare(` SELECT custom_picture, name FROM users WHERE id = ?; `) if err != nil { return picture, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() customPicture := false name := "" err = stmt.QueryRow(user).Scan(&customPicture, &name) if err != nil { return picture, util.NewError(err, "Usuario no encontrado", 500) } sizeSuffix := "" if size != "100" { sizeSuffix = "_" + size } if customPicture { picture = "https://5sur.com/images/" + name + sizeSuffix + ".png" } else { picture = "https://5sur.com/default" + sizeSuffix + ".png" } return picture, nil }
func UploadDeleteHandler(w http.ResponseWriter, r *http.Request) error { // Database initialization db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication user, userId, _, err := util.CheckCookie(r, db) // return "" if not logged in if err != nil { return err } if userId == 0 { return util.NewError(nil, "Se requiere ingreso a la cuenta", 401) } if r.FormValue("User") != strconv.Itoa(userId) { return util.NewError(nil, "Foto no borrado", 400) } err = util.DeletePicture(db, user) if err != nil { return err } http.Redirect(w, r, "https://5sur.com/dashboard/settings", 303) return nil }
func LoginHandler(w http.ResponseWriter, r *http.Request) error { // POST validation if r.FormValue("Password") == "" || r.FormValue("Username") == "" { return util.NewError(nil, "Missing username or password", 400) } // Database initialization db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication authenticated, err := gen.CheckCredentials(db, r.FormValue("Username"), r.FormValue("Password")) if err != nil { return err } if authenticated { myCookie, err := util.CreateCookie(r.FormValue("Username"), db, true, true) // This also stores a hashed cookie in the database if err != nil { return err } http.SetCookie(w, &myCookie) w.WriteHeader(200) fmt.Fprint(w, "Logged in as "+r.FormValue("Username")) return nil } else { return util.NewError(nil, "Your username or password was incorrect", 400) } return nil }
func SendMessageHandler(w http.ResponseWriter, r *http.Request) error { recipientId, err := util.ValidMessageURL(r) if err != nil { return err } db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication _, userId, _, err := util.CheckCookie(r, db) // return "" if not logged in if err != nil { return err } if userId == 0 { return util.NewError(nil, "Se requiere ingreso a la cuenta", 401) } userInfo, err := gen.ReturnUserInfo(db, recipientId) if err != nil { return err } err = templates.ExecuteTemplate(w, "message.html", userInfo) if err != nil { return util.NewError(err, "No se cargó la página", 500) } return nil }
func MessageLimit(db *sql.DB, user int) error { stmt, err := db.Prepare(` SELECT count(*) FROM messages WHERE sender = ? AND date BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW(); `) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() messagesPastHour := 0 err = stmt.QueryRow(user).Scan(&messagesPastHour) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } if messagesPastHour >= 50 { return util.NewError(err, "Has superado el limite de mensajes permitido para 24 horas. Intenta más tarde.", 400) } return nil }
func returnRideHistory(db *sql.DB, u int) (map[string]int, map[string]int, error) { result := make(map[string]int) result2 := make(map[string]int) stmt, err := db.Prepare(` SELECT year, sum(rides_given), sum(rides_taken) FROM ride_history WHERE user_id = ? GROUP BY year; `) if err != nil { return result, result2, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() rows, err := stmt.Query(u) if err != nil { return result, result2, util.NewError(err, "Error de la base de datos", 500) } defer rows.Close() for rows.Next() { year := "" var given, taken int err := rows.Scan(&year, &given, &taken) if err != nil { return result, result2, util.NewError(err, "Error de la base de datos", 500) } result[year] = given result2[year] = taken } return result, result2, nil }
func UploadHandler(w http.ResponseWriter, r *http.Request) error { // Database initialization db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication user, _, _, err := util.CheckCookie(r, db) // return "" if not logged in if err != nil { return err } if user == "" { return util.NewError(nil, "Se requiere ingreso a la cuenta", 401) } // the FormFile function takes in the POST input id file file, header, err := r.FormFile("Picture") if err != nil { return util.NewError(nil, "Foto no encontrado", 400) } defer file.Close() err = util.SaveImage(db, user, file, header) if err != nil { return err } http.Redirect(w, r, "https://5sur.com/dashboard/settings", 303) return nil }
func addToReservation(db *sql.DB, userId int, listingId int, passengerId int, seats int) error { stmt, err := db.Prepare(` INSERT INTO reservations(listing_id, driver_id, passenger_id, seats) SELECT ? AS listing_id, ? AS driver_id, ? AS passenger_id, ? AS seats FROM dual WHERE NOT EXISTS ( SELECT listing_id FROM reservations WHERE listing_id = ? AND driver_id = ? AND passenger_id = ? ) LIMIT 1; `) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() // db.Query() prepares, executes, and closes a prepared statement - three round // trips to the databse. Call it infrequently as possible; use efficient SQL statments _, err = stmt.Exec(listingId, userId, passengerId, seats, listingId, userId, passengerId) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } return nil }
func getMessageExpiration(db *sql.DB, recipient int, userId int) (string, error) { expiration := "" stmt, err := db.Prepare(` SELECT DATE_FORMAT(DATE_ADD(l.date_leaving, INTERVAL 1 WEEK), "%d-%m-%Y %H:%I") FROM reservations AS r JOIN listings AS l ON r.listing_id = l.id WHERE (r.passenger_id = ? AND r.driver_id = ?) OR (r.passenger_id = ? AND r.driver_id = ?) ORDER BY l.date_leaving DESC LIMIT 1; `) if err != nil { return expiration, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() err = stmt.QueryRow(recipient, userId, userId, recipient).Scan(&expiration) if err != nil { return expiration, util.NewError(nil, "No tienes permiso mandar mensajes a esta persona", 400) } return expiration, nil }
func getPendingUser(db *sql.DB, listingId int, pendingUserId int) (PendingUser, error) { stmt, err := db.Prepare(` SELECT r.message, u.id, u.name, u.custom_picture, r.seats FROM reservation_queue as r JOIN users AS u ON r.passenger_id = u.id WHERE r.listing_id = ? AND u.id = ?; `) if err != nil { return PendingUser{}, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() pendingUser := PendingUser{} customPicture := false err = stmt.QueryRow(listingId, pendingUserId).Scan(&pendingUser.Message, &pendingUser.Id, &pendingUser.Name, &customPicture, &pendingUser.Seats) if err != nil { return pendingUser, util.NewError(nil, "Usuario no existe", 400) } if customPicture { pendingUser.Picture = "https://5sur.com/images/" + pendingUser.Name + "_50.png" } else { pendingUser.Picture = "https://5sur.com/default_50.png" } return pendingUser, nil }
func CheckCaptcha(formValue string, userIp string) (bool, error) { secretKey, err := ioutil.ReadFile("captchaPassword") if err != nil { return false, util.NewError(err, "Error de servidor", 500) } resp, err := http.Get("https://www.google.com/recaptcha/api/siteverify?secret=" + string(secretKey[:]) + "&response=" + formValue + "&remoteip=" + userIp) if err != nil { return false, util.NewError(err, "Verificación de captcha incorrecta", 500) } defer resp.Body.Close() contents, err := ioutil.ReadAll(resp.Body) if err != nil { return false, util.NewError(err, "Verificación de captcha incorrecta", 500) } type Captcha struct { Success bool ErrorCodes []string } var captcha Captcha err = json.Unmarshal(contents, &captcha) if err != nil { return false, util.NewError(err, "Verificación de captcha incorrecta", 500) } return captcha.Success, nil }
func CheckUserInfo(db *sql.DB, username string, email string) error { err := invalidUsername(username) if err != nil { return err } err = invalidEmail(email) if err != nil { return err } unused, err := unusedEmail(db, email) if err != nil { return err } if unused != "" { return util.NewError(nil, "Email is already in use", 400) } uniqueUsername, err := unusedUsername(db, username) if err != nil { return err } if !uniqueUsername { return util.NewError(nil, "Nombre ya en uso", 400) } return nil }
func returnUserComments(db *sql.DB, u int) ([]Comment, error) { results := make([]Comment, 0) stmt, err := db.Prepare(` SELECT positive, DATE_FORMAT(date,'%d/%m/%Y'), comment FROM comments WHERE user = ? AND public = true; `) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() rows, err := stmt.Query(u) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } defer rows.Close() for rows.Next() { comment := Comment{} err := rows.Scan(&comment.Positive, &comment.Date, &comment.Text) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } results = append(results, comment) } return results, nil }
func unusedUsername(db *sql.DB, username string) (bool, error) { // Always prepare queries to be used multiple times. The parameter placehold is ? stmt, err := db.Prepare(` SELECT users.name FROM users WHERE users.name = ? `) if err != nil { return false, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() // db.Query() prepares, executes, and closes a prepared statement - three round // trips to the databse. Call it infrequently as possible; use efficient SQL statments rows, err := stmt.Query(username) if err != nil { return false, util.NewError(err, "Error de la base de datos", 500) } // Always defer rows.Close(), even if you explicitly Close it at the end of the // loop. The connection will have the chance to remain open otherwise. defer rows.Close() // The last rows.Next() call will encounter an EOF error and call rows.Close() for rows.Next() { return false, nil } return true, nil }
func createUserAuth(db *sql.DB, username string, password string, email string, auth string) error { // Always prepare queries to be used multiple times. The parameter placehold is ? stmt, err := db.Prepare(` INSERT INTO unauthed_users (name, email, password, auth) VALUES (?, ?, ?, ?) `) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() hashedPassword, err := hashPassword(password) if err != nil { return err } res, err := stmt.Exec(username, email, hashedPassword, auth) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } _, err = res.RowsAffected() if err != nil { return util.NewError(err, "Error de la base de datos", 500) } return nil }
func EmailPrefHandler(w http.ResponseWriter, r *http.Request) error { db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication _, userId, _, err := util.CheckCookie(r, db) // return "" if not logged in if err != nil { return err } if userId == 0 { return util.NewError(nil, "Se requiere ingreso a la cuenta", 401) } prefs, err := util.ReturnEmailPref(db, userId) if err != nil { return err } err = templates.ExecuteTemplate(w, "emailPref.html", prefs) if err != nil { return util.NewError(err, "No se cargó la página", 500) } return nil }
func ReturnFilter(db *sql.DB) ([]City, error) { results := make([]City, 0) // Always prepare queries to be used multiple times. The parameter placehold is ? stmt, err := db.Prepare(` SELECT * from cities ORDER BY name `) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() // db.Query() prepares, executes, and closes a prepared statement - three round // trips to the databse. Call it infrequently as possible; use efficient SQL statments rows, err := stmt.Query() if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } // Always defer rows.Close(), even if you explicitly Close it at the end of the // loop. The connection will have the chance to remain open otherwise. defer rows.Close() // The last rows.Next() call will encounter an EOF error and call rows.Close() for rows.Next() { var temp City err := rows.Scan(&temp.Id, &temp.Name) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } results = append(results, temp) } return results, nil }
func CreateListingHandler(w http.ResponseWriter, r *http.Request) error { // Database initialization db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication user, _, _, err := util.CheckCookie(r, db) // return "" if not logged in if err != nil { return err } if user == "" { return util.NewError(nil, "Se requiere ingreso a la cuenta", 401) } // HTML generation (also does listing-specific SQL calls) cities, err := gen.ReturnFilter(db) if err != nil { return err } err = templates.ExecuteTemplate(w, "create.html", cities) if err != nil { return util.NewError(err, "No se cargó la página", 500) } return nil }
func SetMessagesClosed(db *sql.DB, sender int, receiver int) error { stmt, err := db.Prepare(` UPDATE messages SET opened = 1 WHERE sender = ? AND receiver = ? `) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() // db.Query() prepares, executes, and closes a prepared statement - three round // trips to the databse. Call it infrequently as possible; use efficient SQL statments res, err := stmt.Exec(sender, receiver) if err != nil { return util.NewError(err, "Error de la base de datos", 500) } _, err = res.RowsAffected() if err != nil { return util.NewError(err, "Error de la base de datos", 500) } return nil }
func ReturnListings(db *sql.DB, o int, d int, t string) ([]Listing, error) { results := make([]Listing, 0) // Always prepare queries to be used multiple times. The parameter placehold is ? stmt, err := db.Prepare(` SELECT l.id, u.id, u.name, u.custom_picture, (u.positive_ratings - u.negative_ratings), l.date_leaving, c.name, c2.name, l.seats, l.fee FROM listings AS l JOIN users AS u ON l.driver = u.id JOIN cities AS c ON l.origin = c.id LEFT JOIN cities AS c2 ON l.destination = c2.id WHERE l.origin = ? AND l.destination = ? AND l.date_leaving >= ? AND l.seats > 0 AND l.date_leaving > NOW() ORDER BY l.date_leaving LIMIT 50 `) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } defer stmt.Close() // db.Query() prepares, executes, and closes a prepared statement - three round // trips to the databse. Call it infrequently as possible; use efficient SQL statments rows, err := stmt.Query(o, d, t) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } // Always defer rows.Close(), even if you explicitly Close it at the end of the // loop. The connection will have the chance to remain open otherwise. defer rows.Close() // The last rows.Next() call will encounter an EOF error and call rows.Close() for rows.Next() { var temp Listing customPicture := false name := "" err := rows.Scan(&temp.Id, &temp.Driver, &name, &customPicture, &temp.Rating, &temp.Timestamp, &temp.Origin, &temp.Destination, &temp.Seats, &temp.Fee) if err != nil { return results, util.NewError(err, "Error de la base de datos", 500) } prettyTime, err := util.PrettyDate(temp.Timestamp, false) if err != nil { return results, err } temp.Date = prettyTime.Month + " " + prettyTime.Day temp.Time = prettyTime.Time if customPicture { temp.Picture = "https://5sur.com/images/" + name + "_50.png" } else { temp.Picture = "https://5sur.com/default_50.png" } results = append(results, temp) } return results, nil }
func CreateUser(db *sql.DB, token string) (string, error) { hashed := sha256.New() hashed.Write([]byte(token)) hashedStr := hex.EncodeToString(hashed.Sum(nil)) stmt, err := db.Prepare(` SELECT u.name, u.email, u.password, u.auth FROM unauthed_users AS u WHERE u.auth = ? `) if err != nil { return "", util.NewError(err, "Usuario no creado", 500) } defer stmt.Close() rows, err := stmt.Query(hashedStr) if err != nil { return "", util.NewError(err, "Usuario no creado", 500) } defer rows.Close() // The last rows.Next() call will encounter an EOF error and call rows.Close() userInfo := unauthedUser{} for rows.Next() { err := rows.Scan(&userInfo.name, &userInfo.email, &userInfo.password, &userInfo.auth) if err != nil { return "", util.NewError(err, "Usuario no creado", 500) } } if userInfo.name == "" { return "", util.NewError(nil, "Verificación incorrecta ", 400) } // Always run this check before creating a user (which should only be here anyway) uniqueUsername, err := unusedUsername(db, userInfo.name) if err != nil { return "", err } if !uniqueUsername { err = deleteUserAuth(db, userInfo.email) return "", util.NewError(nil, "Nombre ya en uso", 400) } userId, err := createUser(db, userInfo) if err != nil { return "", err } err = util.CreateEmailPrefs(db, userId) if err != nil { return "", err } return userInfo.name, nil }
func invalidUsername(username string) error { valid, err := regexp.Match("^[a-zA-ZÁÉÍÓÑÚÜáéíóñúü0-9_-]{3,20}$", []byte(username)) if err != nil { return util.NewError(err, "Error de servidor", 500) } if valid { return nil } return util.NewError(nil, "Usuario invalido", 400) }
func invalidEmail(email string) error { valid, err := regexp.Match(`\S+\@\S+\.\S`, []byte(email)) if err != nil { return util.NewError(err, "Error de servidor", 500) } if valid { return nil } return util.NewError(nil, "Correo electrónico invalido", 400) }
func DeleteAccountHandler(w http.ResponseWriter, r *http.Request) error { db, err := util.OpenDb() if err != nil { return err } defer db.Close() // User authentication user, userId, _, err := util.CheckCookie(r, db) // return "" if not logged in if err != nil { return err } if user == "" { return util.NewError(nil, "Se requiere ingreso a la cuenta", 401) } // put this in valid if r.FormValue("Password") == "" || r.FormValue("Password2") == "" { return util.NewError(nil, "Rellena el formulario completo por favor", 400) } if r.FormValue("Password") != r.FormValue("Password2") { return util.NewError(nil, "No coincide la contraseña", 400) } authenticated, err := gen.CheckCredentials(db, user, r.FormValue("Password")) if err != nil { return err } if !authenticated { return util.NewError(nil, "Contraseña incorrecta", 400) } err = gen.DeleteAccount(db, userId) if err != nil { return err } Page := struct { Title string MessageTitle string Message string }{ "Borrar cuenta", "", "Cuenta eliminada", } err = templates.ExecuteTemplate(w, "formSubmit.html", Page) if err != nil { return util.NewError(err, "No se cargó la página", 500) } return nil }
func RegistrationHandler(w http.ResponseWriter, r *http.Request) error { err := util.ValidRegister(r) if err != nil { return err } // Database initialization db, err := util.OpenDb() if err != nil { return err } defer db.Close() err = gen.CheckUserInfo(db, r.FormValue("Username"), r.FormValue("Email")) if err != nil { return err } userIp := "" if ipProxy := r.Header.Get("X-Real-IP"); len(ipProxy) > 0 { userIp = ipProxy } else { userIp, _, _ = net.SplitHostPort(r.RemoteAddr) } human, err := gen.CheckCaptcha(r.FormValue("g-recaptcha-response"), userIp) if err != nil { return err } if !human { return util.NewError(nil, "Captcha invalido", 400) } err = gen.UserAuth(db, r.FormValue("Username"), r.FormValue("Password"), r.FormValue("Email")) if err != nil { return err } Page := struct { Title string MessageTitle string Message string }{ "Regístrate", "", "Email de confirmacion ha sido mandado a " + r.FormValue("Email"), } err = templates.ExecuteTemplate(w, "formSubmit.html", Page) if err != nil { return util.NewError(err, "No se cargó la página", 500) } return nil }