cspj-application/server/internal/sql_injection/sql_injection.go

package sql_injection

import (
	"bytes"
	"context"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"regexp"

	"github.com/Vomitblood/cspj-application/server/internal/db"
)

// secure register endpoint
func UnsecureRegisterSql(w http.ResponseWriter, r *http.Request) {
	// read the request body
	var credentials struct {
		Email      string `json:"email"`
		Password   string `json:"password"`
		RePassword string `json:"rePassword"`
	}

	if err := json.NewDecoder(r.Body).Decode(&credentials); err != nil {
		http.Error(w, "Invalid request format", http.StatusBadRequest)
		return
	}
	defer r.Body.Close()

	// check if the email is an email using regex, if not reject
	emailRegex := `^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$`
	re := regexp.MustCompile(emailRegex)
	if !re.MatchString(credentials.Email) {
		http.Error(w, "Invalid email format", http.StatusBadRequest)
		return
	}

	// check if the password matches, if not reject
	if credentials.Password != credentials.RePassword {
		http.Error(w, "Passwords do not match", http.StatusBadRequest)
		return
	}

	// get the number of emails that matches
	// construct the sql query using concatenation, BAD
	emailCheckSQL := fmt.Sprintf("SELECT COUNT(*) FROM users WHERE email = '%s'", credentials.Email)
	var existingUserCount int
	err := db.DbPool.QueryRow(context.Background(), emailCheckSQL).Scan(&existingUserCount)
	if err != nil {
		http.Error(w, "Error checking email in the database", http.StatusInternalServerError)
		log.Printf("Error checking email: %v", err)
		return
	}
	// if there is more than 0 matches, that means email already exists, reject
	if existingUserCount > 0 {
		http.Error(w, "Email already exists", http.StatusConflict)
		return
	}

	// over here the validations has passed, so insert into the db
	// also use concatenation here
	insertSQL := fmt.Sprintf("INSERT INTO users (email, password, role) VALUES ('%s', '%s', 'user')", credentials.Email, credentials.Password)
	_, err = db.DbPool.Exec(context.Background(), insertSQL)
	if err != nil {
		http.Error(w, "Error inserting user into the database", http.StatusInternalServerError)
		log.Printf("Error inserting user: %v", err)
		return
	}

	// send back status ok
	w.WriteHeader(http.StatusOK)
	w.Write([]byte("User registered successfully"))
	log.Println("User registered successfully:", credentials.Email)
}

// secure register endpoint
func SecureRegisterSql(w http.ResponseWriter, r *http.Request) {
	// read the request body
	var credentials struct {
		Email      string `json:"email"`
		Password   string `json:"password"`
		RePassword string `json:"rePassword"`
	}

	if err := json.NewDecoder(r.Body).Decode(&credentials); err != nil {
		http.Error(w, "Invalid request format", http.StatusBadRequest)
		return
	}
	defer r.Body.Close()

	// check if the email is an email using regex, if not reject
	emailRegex := `^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$`
	re := regexp.MustCompile(emailRegex)
	if !re.MatchString(credentials.Email) {
		http.Error(w, "Invalid email format", http.StatusBadRequest)
		return
	}

	// check if the password matches, if not reject
	if credentials.Password != credentials.RePassword {
		http.Error(w, "Passwords do not match", http.StatusBadRequest)
		return
	}

	// get the number of emails that matches
	// use parameterization
	var existingUserCount int
	emailCheckSQL := `SELECT COUNT(*) FROM users WHERE email = $1`
	err := db.DbPool.QueryRow(context.Background(), emailCheckSQL, credentials.Email).Scan(&existingUserCount)
	if err != nil {
		http.Error(w, "Error checking email in the database", http.StatusInternalServerError)
		log.Printf("Error checking email: %v", err)
		return
	}
	// if there is more than 0 matches, that means email already exists, reject
	if existingUserCount > 0 {
		http.Error(w, "Email already exists", http.StatusConflict)
		return
	}

	// over here the validations has passed, so insert into the db
	// use parameterization
	insertSQL := `INSERT INTO users (email, password, role) VALUES ($1, $2, $3)`
	_, err = db.DbPool.Exec(context.Background(), insertSQL, credentials.Email, credentials.Password, "user")
	if err != nil {
		http.Error(w, "Error inserting user into the database", http.StatusInternalServerError)
		log.Printf("Error inserting user: %v", err)
		return
	}

	// send back status ok
	w.WriteHeader(http.StatusOK)
	w.Write([]byte("User registered successfully"))
	log.Println("User registered successfully:", credentials.Email)
}

func UnsecureLoginSql(w http.ResponseWriter, r *http.Request) {
	log.Println("/unsecure-login-sql hit")
	// decode the json body
	var requestData struct {
		Email    string `json:"email"`
		Password string `json:"password"`
	}

	// decode the request body
	if err := json.NewDecoder(r.Body).Decode(&requestData); err != nil {
		http.Error(w, "Invalid request format", http.StatusBadRequest)
		log.Println("Failed to decode JSON body:", err)
		return
	}

	// make insecure sql command
	query := fmt.Sprintf("SELECT id, email, password FROM users WHERE email = '%s' AND password = '%s'", requestData.Email, requestData.Password)

	var id int
	var email string
	var password string

	// execute the query to retrieve the data
	err := db.DbPool.QueryRow(context.Background(), query).Scan(&id, &email, &password)
	if err != nil {
		http.Error(w, "Invalid email or password", http.StatusUnauthorized)
		log.Println("Error during query execution:", err)
		return
	}

	// if the email and password match then send back the user data
	response := map[string]interface{}{
		"id":    id,
		"email": email,
	}
	if err := json.NewEncoder(w).Encode(response); err != nil {
		http.Error(w, "Failed to encode response as JSON", http.StatusInternalServerError)
		log.Printf("JSON encoding error: %v", err)
	}
}

// very secure login endpoint
func SecureLoginSql(w http.ResponseWriter, r *http.Request) {
	log.Println("/secure-login-sql hit")
	var requestData struct {
		Email    string `json:"email"`
		Password string `json:"password"`
	}

	type AIResponse struct {
		Confidence float64 `json:"confidence"`
		Query      string  `json:"query"`
		Result     string  `json:"result"`
	}

	// declare new json decoder with custom property
	jsonDecoder := json.NewDecoder(r.Body)
	// rejects any unknown fields in the json, more strict
	jsonDecoder.DisallowUnknownFields()

	if err := json.NewDecoder(r.Body).Decode(&requestData); err != nil {
		http.Error(w, "Invalid request format", http.StatusBadRequest)
		log.Println("Failed to decode JSON body or extra fields present:", err)
		return
	}

	// validate that user is provided
	if requestData.Email == "" || requestData.Password == "" {
		http.Error(w, "Invalid request format", http.StatusBadRequest)
		return
	}

	// retrieve list of existing emails from db
	existingEmails, err := db.FetchEmails()
	if err != nil {
		http.Error(w, "Internal server error", http.StatusInternalServerError)
		log.Printf("Failed to fetch emails: %v", err)
		return
	}

	// check if the email exists in the allowed list
	// this step is crucial
	// server will reject ANYTHING that does not match the list
	if !existingEmails[requestData.Email] {
		http.Error(w, "Invalid email or password", http.StatusBadRequest)
		return
	}

	// make insecure sql command just to send to ai model to check
	insecureQuery := fmt.Sprintf("SELECT id, email, password FROM users WHERE email = '%s' AND password = '%s'", requestData.Email, requestData.Password)

	// send the query to the ai model server
	aiReqBody, _ := json.Marshal(map[string]string{"query": insecureQuery})
	aiResp, err := http.Post("http://127.0.0.1:5000/predict", "application/json", bytes.NewBuffer(aiReqBody))
	if err != nil {
		http.Error(w, "Internal Server Error", http.StatusInternalServerError)
		log.Println("Failed to reach AI model:", err)
		return
	}
	defer aiResp.Body.Close()

	var aiResponse AIResponse
	if err := json.NewDecoder(aiResp.Body).Decode(&aiResponse); err != nil {
		http.Error(w, "Failed to decode AI response", http.StatusInternalServerError)
		log.Println("Error decoding AI response:", err)
		return
	}

	log.Println("AI Response:", aiResponse.Result)

	// reject the original request if ai result is fail
	if aiResponse.Result == "fail" {
		http.Error(w, "Potential SQL injection detected", http.StatusForbidden)
		log.Println("AI detected SQL injection attempt")
		return
	}

	// retrieve the email and password from the database
	var id int
	var email string
	var storedPassword string
	query := "SELECT id, email, password FROM users WHERE email = $1"
	err = db.DbPool.QueryRow(context.Background(), query, requestData.Email).Scan(&id, &email, &storedPassword)
	if err != nil {
		http.Error(w, "Invalid email or password", http.StatusNotFound)
		return
	}

	// compare the provided password with the stored plain-text password
	if storedPassword != requestData.Password {
		http.Error(w, "Invalid email or password", http.StatusUnauthorized)
		return
	}

	// send back the user data as a json response
	response := map[string]interface{}{
		"id":    id,
		"email": email,
	}
	if err := json.NewEncoder(w).Encode(response); err != nil {
		http.Error(w, "Failed to encode response as JSON", http.StatusInternalServerError)
		log.Printf("JSON encoding error: %v", err)
	}
}