db.go (7277B) - raw
1 package main 2 3 import ( 4 "crypto/rand" 5 "database/sql" 6 "fmt" 7 "io" 8 "io/ioutil" 9 "log" 10 "os" 11 "path" 12 "path/filepath" 13 "sort" 14 "strings" 15 "time" 16 17 "golang.org/x/crypto/bcrypt" 18 ) 19 20 var DB *sql.DB 21 22 func initializeDB() { 23 var err error 24 DB, err = sql.Open("sqlite3", c.DBFile) 25 if err != nil { 26 log.Fatal(err) 27 } 28 createTablesIfDNE() 29 } 30 31 // returns nil if login OK, err otherwise 32 // log in with email or username 33 func checkLogin(name string, password string) (string, bool, error) { 34 row := DB.QueryRow("SELECT username, password_hash, active, admin FROM user where username = $1 OR email = $1", name) 35 var db_password []byte 36 var username string 37 var active bool 38 var isAdmin bool 39 err := row.Scan(&username, &db_password, &active, &isAdmin) 40 if err != nil { 41 if strings.Contains(err.Error(), "no rows") { 42 return username, isAdmin, fmt.Errorf("Username or email '" + name + "' does not exist") 43 } else { 44 return username, isAdmin, err 45 } 46 } 47 if db_password != nil && !active { 48 return username, isAdmin, fmt.Errorf("Your account is not active yet. Pending admin approval") 49 } 50 if bcrypt.CompareHashAndPassword(db_password, []byte(password)) == nil { 51 return username, isAdmin, nil 52 } else { 53 return username, isAdmin, fmt.Errorf("Invalid password") 54 } 55 } 56 57 type File struct { // also folders 58 Creator string 59 Name string // includes folder 60 UpdatedTime time.Time 61 TimeAgo string 62 IsText bool 63 Children []File 64 Host string 65 } 66 67 func fileFromPath(fullPath string) File { 68 info, _ := os.Stat(fullPath) 69 creatorFolder := getCreator(fullPath) 70 updatedTime := info.ModTime() 71 return File{ 72 Name: getLocalPath(fullPath), 73 Creator: path.Base(creatorFolder), 74 UpdatedTime: updatedTime, 75 TimeAgo: timeago(&updatedTime), 76 Host: c.Host, 77 } 78 79 } 80 81 type User struct { 82 Username string 83 Email string 84 Active bool 85 Admin bool 86 CreatedAt int64 // timestamp 87 Reference string 88 Domain string 89 DomainEnabled bool 90 } 91 92 // kinda hacky 93 func getActiveUserNames(all bool) ([]string, error) { 94 var rows *sql.Rows 95 var err error 96 if all { 97 rows, err = DB.Query(`SELECT username from user WHERE active is true`) 98 } else { 99 rows, err = DB.Query(`SELECT username from user WHERE active is true order by random() limit 10`) 100 } 101 if err != nil { 102 return nil, err 103 } 104 var users []string 105 for rows.Next() { 106 var user string 107 err = rows.Scan(&user) 108 if err != nil { 109 return nil, err 110 } 111 users = append(users, user) 112 } 113 114 return users, nil 115 } 116 117 var domains map[string]string 118 119 func refreshDomainMap() error { 120 domains = make(map[string]string) 121 rows, err := DB.Query(`SELECT domain, username from user WHERE domain != ""`) 122 if err != nil { 123 log.Println(err) 124 return err 125 } 126 for rows.Next() { 127 var domain string 128 var username string 129 err = rows.Scan(&domain, &username) 130 if err != nil { 131 return err 132 } 133 domains[domain] = username 134 } 135 return nil 136 } 137 138 func getUserByName(username string) (*User, error) { 139 var user User 140 row := DB.QueryRow(`SELECT username, email, active, admin, created_at, reference, domain, domain_enabled from user WHERE username = ?`, username) 141 err := row.Scan(&user.Username, &user.Email, &user.Active, &user.Admin, &user.CreatedAt, &user.Reference, &user.Domain, &user.DomainEnabled) 142 if err != nil { 143 return nil, err 144 } 145 return &user, nil 146 } 147 148 func getUsers() ([]User, error) { 149 rows, err := DB.Query(`SELECT username, email, active, admin, created_at, reference, domain from user ORDER BY created_at DESC`) 150 if err != nil { 151 return nil, err 152 } 153 var users []User 154 for rows.Next() { 155 var user User 156 err = rows.Scan(&user.Username, &user.Email, &user.Active, &user.Admin, &user.CreatedAt, &user.Reference, &user.Domain) 157 if err != nil { 158 return nil, err 159 } 160 users = append(users, user) 161 } 162 return users, nil 163 } 164 165 // admin tells you whether to show hidden files etc 166 // make sure user is a clean string 167 func getUpdatedFiles(admin bool, user string) ([]*File, error) { // TODO cache this function 168 result := []*File{} 169 dir := c.FilesDirectory 170 if user != "" { 171 dir = path.Join(dir, user) 172 } 173 err := filepath.Walk(dir, func(thepath string, info os.FileInfo, err error) error { 174 if err != nil { 175 log.Printf("Failure accessing a path %q: %v\n", thepath, err) 176 return err // think about 177 } 178 if info.Name() == "bl4kers" { 179 // Lazy hack 180 return filepath.SkipDir 181 } 182 if !admin && info.IsDir() && info.Name() == HiddenFolder { 183 return filepath.SkipDir 184 } 185 // make this do what it should 186 if !info.IsDir() && !(strings.HasPrefix(info.Name(), HiddenFolder) && !admin) { 187 res := fileFromPath(thepath) 188 result = append(result, &res) 189 } 190 return nil 191 }) 192 if err != nil { 193 return nil, err 194 } 195 sort.Slice(result, func(i, j int) bool { 196 return result[i].UpdatedTime.After(result[j].UpdatedTime) 197 }) 198 // if many in a row, truncate 199 if user == "" { 200 newResult := []*File{} 201 for _, f := range result { 202 var already bool 203 // slow hack 204 for _, ff := range newResult { 205 if ff.Creator == f.Creator { 206 already = true 207 break 208 } 209 } 210 if !already { 211 newResult = append(newResult, f) 212 } 213 } 214 result = newResult 215 } 216 217 if len(result) > 50 { 218 result = result[:50] 219 } 220 return result, nil 221 } // todo clean up paths 222 223 func getMyFilesRecursive(p string, creator string) ([]File, error) { 224 result := []File{} 225 files, err := ioutil.ReadDir(p) 226 if err != nil { 227 return nil, err 228 } 229 for _, file := range files { 230 fullPath := path.Join(p, file.Name()) 231 f := fileFromPath(fullPath) 232 f.IsText = isTextFile(fullPath) 233 if file.IsDir() { 234 f.Children, err = getMyFilesRecursive(path.Join(p, file.Name()), creator) 235 } 236 result = append(result, f) 237 } 238 return result, nil 239 } 240 241 func createTablesIfDNE() { 242 _, err := DB.Exec(`CREATE TABLE user ( 243 id INTEGER PRIMARY KEY NOT NULL, 244 username TEXT NOT NULL UNIQUE, 245 email TEXT NOT NULL UNIQUE, 246 password_hash TEXT NOT NULL, 247 reference TEXT NOT NULL default "", 248 active boolean NOT NULL DEFAULT false, 249 admin boolean NOT NULL DEFAULT false, 250 created_at INTEGER DEFAULT (strftime('%s', 'now')), 251 domain TEXT NOT NULL default "", 252 domain_enabled BOOLEAN NOT NULL DEFAULT false 253 );`) 254 if err == nil { 255 // on first creation, create admin user with pw admin 256 hashedPassword, err := bcrypt.GenerateFromPassword([]byte("admin"), 8) // TODO handle error 257 if err != nil { 258 log.Fatal(err) 259 } 260 _, err = DB.Exec(`INSERT OR IGNORE INTO user (username, email, password_hash, admin) values ('admin', 'default@flounder.local', ?, true)`, hashedPassword) 261 activateUser("admin") 262 if err != nil { 263 log.Fatal(err) 264 } 265 } 266 267 _, err = DB.Exec(`CREATE TABLE IF NOT EXISTS cookie_key ( 268 value TEXT NOT NULL 269 );`) 270 if err != nil { 271 log.Fatal(err) 272 } 273 } 274 275 // Generate a cryptographically secure key for the cookie store 276 func generateCookieKeyIfDNE() []byte { 277 rows, err := DB.Query("SELECT value FROM cookie_key LIMIT 1") 278 defer rows.Close() 279 if err != nil { 280 log.Fatal(err) 281 } 282 if rows.Next() { 283 var cookie []byte 284 err := rows.Scan(&cookie) 285 if err != nil { 286 log.Fatal(err) 287 } 288 return cookie 289 } else { 290 k := make([]byte, 32) 291 _, err := io.ReadFull(rand.Reader, k) 292 if err != nil { 293 log.Fatal(err) 294 } 295 _, err = DB.Exec("insert into cookie_key values (?)", k) 296 if err != nil { 297 log.Fatal(err) 298 } 299 return k 300 } 301 }