7 files changed, 243 insertions, 95 deletions

diff --git a/shared/server-commands/miscs/sql-command.ts b/shared/server-commands/miscs/sql-command.ts
index 823fc9e38..35cc2253f 100644
--- a/shared/server-commands/miscs/sql-command.ts
+++ b/shared/server-commands/miscs/sql-command.ts
@@ -13,101 +13,87 @@ export class SQLCommand extends AbstractCommand {
     return seq.query(`DELETE FROM "${table}"`, options)
   }
 
-  async getCount (table: string) {
-    const seq = this.getSequelize()
-
-    const options = { type: QueryTypes.SELECT as QueryTypes.SELECT }
-
-    const [ { total } ] = await seq.query<{ total: string }>(`SELECT COUNT(*) as total FROM "${table}"`, options)
+  async getVideoShareCount () {
+    const [ { total } ] = await this.selectQuery<{ total: string }>(`SELECT COUNT(*) as total FROM "videoShare"`)
     if (total === null) return 0
 
     return parseInt(total, 10)
   }
 
   async getInternalFileUrl (fileId: number) {
-    return this.selectQuery(`SELECT "fileUrl" FROM "videoFile" WHERE id = ${fileId}`)
-      .then(rows => rows[0].fileUrl as string)
+    return this.selectQuery<{ fileUrl: string }>(`SELECT "fileUrl" FROM "videoFile" WHERE id = :fileId`, { fileId })
+      .then(rows => rows[0].fileUrl)
   }
 
   setActorField (to: string, field: string, value: string) {
-    const seq = this.getSequelize()
-
-    const options = { type: QueryTypes.UPDATE }
-
-    return seq.query(`UPDATE actor SET "${field}" = '${value}' WHERE url = '${to}'`, options)
+    return this.updateQuery(`UPDATE actor SET ${this.escapeColumnName(field)} = :value WHERE url = :to`, { value, to })
   }
 
   setVideoField (uuid: string, field: string, value: string) {
-    const seq = this.getSequelize()
-
-    const options = { type: QueryTypes.UPDATE }
-
-    return seq.query(`UPDATE video SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options)
+    return this.updateQuery(`UPDATE video SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
   }
 
   setPlaylistField (uuid: string, field: string, value: string) {
-    const seq = this.getSequelize()
-
-    const options = { type: QueryTypes.UPDATE }
-
-    return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options)
+    return this.updateQuery(`UPDATE "videoPlaylist" SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
   }
 
   async countVideoViewsOf (uuid: string) {
-    const seq = this.getSequelize()
-
     const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' +
-      `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = '${uuid}'`
-
-    const options = { type: QueryTypes.SELECT as QueryTypes.SELECT }
-    const [ { total } ] = await seq.query<{ total: number }>(query, options)
+      `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = :uuid`
 
+    const [ { total } ] = await this.selectQuery<{ total: number }>(query, { uuid })
     if (!total) return 0
 
     return forceNumber(total)
   }
 
   getActorImage (filename: string) {
-    return this.selectQuery(`SELECT * FROM "actorImage" WHERE filename = '${filename}'`)
+    return this.selectQuery<{ width: number, height: number }>(`SELECT * FROM "actorImage" WHERE filename = :filename`, { filename })
       .then(rows => rows[0])
   }
 
-  selectQuery (query: string) {
-    const seq = this.getSequelize()
-    const options = { type: QueryTypes.SELECT as QueryTypes.SELECT }
+  // ---------------------------------------------------------------------------
 
-    return seq.query<any>(query, options)
+  setPluginVersion (pluginName: string, newVersion: string) {
+    return this.setPluginField(pluginName, 'version', newVersion)
   }
 
-  updateQuery (query: string) {
-    const seq = this.getSequelize()
-    const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE }
+  setPluginLatestVersion (pluginName: string, newVersion: string) {
+    return this.setPluginField(pluginName, 'latestVersion', newVersion)
+  }
 
-    return seq.query(query, options)
+  setPluginField (pluginName: string, field: string, value: string) {
+    return this.updateQuery(
+      `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`,
+      { pluginName, value }
+    )
   }
 
   // ---------------------------------------------------------------------------
 
-  setPluginField (pluginName: string, field: string, value: string) {
+  selectQuery <T extends object> (query: string, replacements: { [id: string]: string | number } = {}) {
     const seq = this.getSequelize()
+    const options = {
+      type: QueryTypes.SELECT as QueryTypes.SELECT,
+      replacements
+    }
 
-    const options = { type: QueryTypes.UPDATE }
-
-    return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options)
+    return seq.query<T>(query, options)
   }
 
-  setPluginVersion (pluginName: string, newVersion: string) {
-    return this.setPluginField(pluginName, 'version', newVersion)
-  }
+  updateQuery (query: string, replacements: { [id: string]: string | number } = {}) {
+    const seq = this.getSequelize()
+    const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE, replacements }
 
-  setPluginLatestVersion (pluginName: string, newVersion: string) {
-    return this.setPluginField(pluginName, 'latestVersion', newVersion)
+    return seq.query(query, options)
   }
 
   // ---------------------------------------------------------------------------
 
   async getPlaylistInfohash (playlistId: number) {
-    const result = await this.selectQuery('SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = ' + playlistId)
+    const query = 'SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = :playlistId'
+
+    const result = await this.selectQuery<{ p2pMediaLoaderInfohashes: string }>(query, { playlistId })
     if (!result || result.length === 0) return []
 
     return result[0].p2pMediaLoaderInfohashes
@@ -116,19 +102,14 @@ export class SQLCommand extends AbstractCommand {
   // ---------------------------------------------------------------------------
 
   setActorFollowScores (newScore: number) {
-    const seq = this.getSequelize()
-
-    const options = { type: QueryTypes.UPDATE }
-
-    return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options)
+    return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore })
   }
 
   setTokenField (accessToken: string, field: string, value: string) {
-    const seq = this.getSequelize()
-
-    const options = { type: QueryTypes.UPDATE }
-
-    return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options)
+    return this.updateQuery(
+      `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`,
+      { value, accessToken }
+    )
   }
 
   async cleanup () {
@@ -157,4 +138,9 @@ export class SQLCommand extends AbstractCommand {
     return this.sequelize
   }
 
+  private escapeColumnName (columnName: string) {
+    return this.getSequelize().escape(columnName)
+      .replace(/^'/, '"')
+      .replace(/'$/, '"')
+  }
 }
diff --git a/shared/server-commands/requests/requests.ts b/shared/server-commands/requests/requests.ts
index dc9cf4e01..cb0e1a5fb 100644
--- a/shared/server-commands/requests/requests.ts
+++ b/shared/server-commands/requests/requests.ts
@@ -199,7 +199,7 @@ function buildRequest (req: request.Test, options: CommonRequestParams) {
   return req.expect((res) => {
     if (options.expectedStatus && res.status !== options.expectedStatus) {
       throw new Error(`Expected status ${options.expectedStatus}, got ${res.status}. ` +
-        `\nThe server responded this error: "${res.body?.error ?? res.text}".\n` +
+        `\nThe server responded: "${res.body?.error ?? res.text}".\n` +
         'You may take a closer look at the logs. To see how to do so, check out this page: ' +
         'https://github.com/Chocobozzz/PeerTube/blob/develop/support/doc/development/tests.md#debug-server-logs')
     }
diff --git a/shared/server-commands/server/config-command.ts b/shared/server-commands/server/config-command.ts
index 1c2315ed1..eb6bb95a5 100644
--- a/shared/server-commands/server/config-command.ts
+++ b/shared/server-commands/server/config-command.ts
@@ -18,6 +18,33 @@ export class ConfigCommand extends AbstractCommand {
     }
   }
 
+  // ---------------------------------------------------------------------------
+
+  static getEmailOverrideConfig (emailPort: number) {
+    return {
+      smtp: {
+        hostname: '127.0.0.1',
+        port: emailPort
+      }
+    }
+  }
+
+  // ---------------------------------------------------------------------------
+
+  enableSignup (requiresApproval: boolean, limit = -1) {
+    return this.updateExistingSubConfig({
+      newConfig: {
+        signup: {
+          enabled: true,
+          requiresApproval,
+          limit
+        }
+      }
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
   disableImports () {
     return this.setImportsEnabled(false)
   }
@@ -44,6 +71,16 @@ export class ConfigCommand extends AbstractCommand {
     })
   }
 
+  // ---------------------------------------------------------------------------
+
+  enableChannelSync () {
+    return this.setChannelSyncEnabled(true)
+  }
+
+  disableChannelSync () {
+    return this.setChannelSyncEnabled(false)
+  }
+
   private setChannelSyncEnabled (enabled: boolean) {
     return this.updateExistingSubConfig({
       newConfig: {
@@ -56,13 +93,7 @@ export class ConfigCommand extends AbstractCommand {
     })
   }
 
-  enableChannelSync () {
-    return this.setChannelSyncEnabled(true)
-  }
-
-  disableChannelSync () {
-    return this.setChannelSyncEnabled(false)
-  }
+  // ---------------------------------------------------------------------------
 
   enableLive (options: {
     allowReplay?: boolean
@@ -142,6 +173,8 @@ export class ConfigCommand extends AbstractCommand {
     })
   }
 
+  // ---------------------------------------------------------------------------
+
   enableStudio () {
     return this.updateExistingSubConfig({
       newConfig: {
@@ -152,6 +185,8 @@ export class ConfigCommand extends AbstractCommand {
     })
   }
 
+  // ---------------------------------------------------------------------------
+
   getConfig (options: OverrideCommandOptions = {}) {
     const path = '/api/v1/config'
 
@@ -304,6 +339,7 @@ export class ConfigCommand extends AbstractCommand {
       signup: {
         enabled: false,
         limit: 5,
+        requiresApproval: true,
         requiresEmailVerification: false,
         minimumAge: 16
       },
diff --git a/shared/server-commands/server/server.ts b/shared/server-commands/server/server.ts
index ae1395a74..793fae3a8 100644
--- a/shared/server-commands/server/server.ts
+++ b/shared/server-commands/server/server.ts
@@ -18,6 +18,7 @@ import {
   BlocklistCommand,
   LoginCommand,
   NotificationsCommand,
+  RegistrationsCommand,
   SubscriptionsCommand,
   TwoFactorCommand,
   UsersCommand
@@ -147,6 +148,7 @@ export class PeerTubeServer {
   views?: ViewsCommand
   twoFactor?: TwoFactorCommand
   videoToken?: VideoTokenCommand
+  registrations?: RegistrationsCommand
 
   constructor (options: { serverNumber: number } | { url: string }) {
     if ((options as any).url) {
@@ -430,5 +432,6 @@ export class PeerTubeServer {
     this.views = new ViewsCommand(this)
     this.twoFactor = new TwoFactorCommand(this)
     this.videoToken = new VideoTokenCommand(this)
+    this.registrations = new RegistrationsCommand(this)
   }
 }
diff --git a/shared/server-commands/users/index.ts b/shared/server-commands/users/index.ts
index 1afc02dc1..404756539 100644
--- a/shared/server-commands/users/index.ts
+++ b/shared/server-commands/users/index.ts
@@ -4,6 +4,7 @@ export * from './blocklist-command'
 export * from './login'
 export * from './login-command'
 export * from './notifications-command'
+export * from './registrations-command'
 export * from './subscriptions-command'
 export * from './two-factor-command'
 export * from './users-command'
diff --git a/shared/server-commands/users/registrations-command.ts b/shared/server-commands/users/registrations-command.ts
new file mode 100644
index 000000000..f57f54b34
--- /dev/null
+++ b/shared/server-commands/users/registrations-command.ts
@@ -0,0 +1,151 @@
+import { pick } from '@shared/core-utils'
+import { HttpStatusCode, ResultList, UserRegistration, UserRegistrationRequest, UserRegistrationUpdateState } from '@shared/models'
+import { unwrapBody } from '../requests'
+import { AbstractCommand, OverrideCommandOptions } from '../shared'
+
+export class RegistrationsCommand extends AbstractCommand {
+
+  register (options: OverrideCommandOptions & Partial<UserRegistrationRequest> & Pick<UserRegistrationRequest, 'username'>) {
+    const { password = 'password', email = options.username + '@example.com' } = options
+    const path = '/api/v1/users/register'
+
+    return this.postBodyRequest({
+      ...options,
+
+      path,
+      fields: {
+        ...pick(options, [ 'username', 'displayName', 'channel' ]),
+
+        password,
+        email
+      },
+      implicitToken: false,
+      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
+    })
+  }
+
+  requestRegistration (
+    options: OverrideCommandOptions & Partial<UserRegistrationRequest> & Pick<UserRegistrationRequest, 'username' | 'registrationReason'>
+  ) {
+    const { password = 'password', email = options.username + '@example.com' } = options
+    const path = '/api/v1/users/registrations/request'
+
+    return unwrapBody<UserRegistration>(this.postBodyRequest({
+      ...options,
+
+      path,
+      fields: {
+        ...pick(options, [ 'username', 'displayName', 'channel', 'registrationReason' ]),
+
+        password,
+        email
+      },
+      implicitToken: false,
+      defaultExpectedStatus: HttpStatusCode.OK_200
+    }))
+  }
+
+  // ---------------------------------------------------------------------------
+
+  accept (options: OverrideCommandOptions & { id: number } & UserRegistrationUpdateState) {
+    const { id } = options
+    const path = '/api/v1/users/registrations/' + id + '/accept'
+
+    return this.postBodyRequest({
+      ...options,
+
+      path,
+      fields: pick(options, [ 'moderationResponse', 'preventEmailDelivery' ]),
+      implicitToken: true,
+      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
+    })
+  }
+
+  reject (options: OverrideCommandOptions & { id: number } & UserRegistrationUpdateState) {
+    const { id } = options
+    const path = '/api/v1/users/registrations/' + id + '/reject'
+
+    return this.postBodyRequest({
+      ...options,
+
+      path,
+      fields: pick(options, [ 'moderationResponse', 'preventEmailDelivery' ]),
+      implicitToken: true,
+      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
+  delete (options: OverrideCommandOptions & {
+    id: number
+  }) {
+    const { id } = options
+    const path = '/api/v1/users/registrations/' + id
+
+    return this.deleteRequest({
+      ...options,
+
+      path,
+      implicitToken: true,
+      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
+  list (options: OverrideCommandOptions & {
+    start?: number
+    count?: number
+    sort?: string
+    search?: string
+  } = {}) {
+    const path = '/api/v1/users/registrations'
+
+    return this.getRequestBody<ResultList<UserRegistration>>({
+      ...options,
+
+      path,
+      query: pick(options, [ 'start', 'count', 'sort', 'search' ]),
+      implicitToken: true,
+      defaultExpectedStatus: HttpStatusCode.OK_200
+    })
+  }
+
+  // ---------------------------------------------------------------------------
+
+  askSendVerifyEmail (options: OverrideCommandOptions & {
+    email: string
+  }) {
+    const { email } = options
+    const path = '/api/v1/users/registrations/ask-send-verify-email'
+
+    return this.postBodyRequest({
+      ...options,
+
+      path,
+      fields: { email },
+      implicitToken: false,
+      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
+    })
+  }
+
+  verifyEmail (options: OverrideCommandOptions & {
+    registrationId: number
+    verificationString: string
+  }) {
+    const { registrationId, verificationString } = options
+    const path = '/api/v1/users/registrations/' + registrationId + '/verify-email'
+
+    return this.postBodyRequest({
+      ...options,
+
+      path,
+      fields: {
+        verificationString
+      },
+      implicitToken: false,
+      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
+    })
+  }
+}
diff --git a/shared/server-commands/users/users-command.ts b/shared/server-commands/users/users-command.ts
index 811b9685b..8a42fafc8 100644
--- a/shared/server-commands/users/users-command.ts
+++ b/shared/server-commands/users/users-command.ts
@@ -214,35 +214,6 @@ export class UsersCommand extends AbstractCommand {
     return this.server.login.getAccessToken({ username, password })
   }
 
-  register (options: OverrideCommandOptions & {
-    username: string
-    password?: string
-    displayName?: string
-    email?: string
-    channel?: {
-      name: string
-      displayName: string
-    }
-  }) {
-    const { username, password = 'password', displayName, channel, email = username + '@example.com' } = options
-    const path = '/api/v1/users/register'
-
-    return this.postBodyRequest({
-      ...options,
-
-      path,
-      fields: {
-        username,
-        password,
-        email,
-        displayName,
-        channel
-      },
-      implicitToken: false,
-      defaultExpectedStatus: HttpStatusCode.NO_CONTENT_204
-    })
-  }
-
   // ---------------------------------------------------------------------------
 
   getMyInfo (options: OverrideCommandOptions = {}) {