Substitute parameters in SQL query on the database side

[davidyuk]

This should be used as protection against SQL injections instead of manual ones

tokaen-api/src/tokens/tokens.controller.ts

Lines 86 to 98 in 77c9ccb

	// allowed sort fields to avoid SQL Injection
	const allowedSortFields = [
	'market_cap',
	'rank',
	'name',
	'price',
	'created_at',
	'trending_score',
	];
	if (!allowedSortFields.includes(orderBy)) {
	orderBy = 'market_cap';
	}
	const allowedOrderDirections = ['ASC', 'DESC'];

For example,

tokaen-api/src/tokens/tokens.service.ts

Lines 505 to 529 in 77c9ccb

	const rankedQuery = `
	WITH all_ranked_tokens AS (
	SELECT
	*,
	CAST(RANK() OVER (
	ORDER BY
	CASE WHEN market_cap = 0 THEN 1 ELSE 0 END,
	market_cap DESC,
	created_at ASC
	) AS INTEGER) as rank
	FROM token
	WHERE unlisted = false
	),
	filtered_tokens AS (
	${finalSubQuery}
	)
	SELECT all_ranked_tokens.*
	FROM all_ranked_tokens
	INNER JOIN filtered_tokens ON all_ranked_tokens.sale_address = filtered_tokens.sale_address
	ORDER BY all_ranked_tokens.${orderBy} ${orderDirection}
	LIMIT ${limit}
	OFFSET ${(page - 1) * limit}
	`;
	const result = await this.tokensRepository.query(rankedQuery);

replace with

    const rankedQuery = `
      WITH all_ranked_tokens AS (
        SELECT 
          *,
          CAST(RANK() OVER (
            ORDER BY 
              CASE WHEN market_cap = 0 THEN 1 ELSE 0 END,
              market_cap DESC,
              created_at ASC
          ) AS INTEGER) as rank
        FROM token
        WHERE unlisted = false
      ),
      filtered_tokens AS (
        ${finalSubQuery}
      )
      SELECT all_ranked_tokens.*
      FROM all_ranked_tokens
      INNER JOIN filtered_tokens ON all_ranked_tokens.sale_address = filtered_tokens.sale_address
      ORDER BY $1 ${orderDirection}
      LIMIT ${limit}
      OFFSET ${(page - 1) * limit}
    `;

    const result = await this.tokensRepository.query(rankedQuery, [
      'all_ranked_tokens.' + orderBy,
    ]);

Typeorm docs, see "query - Executes a raw SQL query" examples

found while checking #91