SQL String Formatter

Similar function in SQL Server to string.format in c#

I would use REPLICATE, combined with RIGHT

'C' + RIGHT(REPLICATE('0', <Padding Amount Here>) + CAST(7 AS NVARCHAR(10)), <Padding Amount Here>)

Usage:

'C' + RIGHT(REPLICATE('0', 10) + CAST(7 AS NVARCHAR(10)), 10)

Produces the follow:

C0000000007

How to format java string to SQL syntax?

You can go for the SQLFormatter provided by apache openJPA project

formatting the long sql string which contains double quotes

Apparently your problem has nothing to do with Winforms.

just i don't know to how to format it to include double quotes

In SQL string quotes are with single quotes, like this example from SQL IN operator

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

So if you need this command text in a c# string:

const string sqlCommandText = @"
    SELECT * FROM Customers
    WHERE Country IN ('Germany', 'France', 'UK')";

cmd.CommandText = sqlCommandText;

Building SQL query string using table-name as given parameter

String building (prone to SQL injection)

What khelwood means:

def selectFrom(table):
    return 'SELECT * FROM ' + table


def see_results(cur, table):
    print("complete")
    cur.execute(selectFrom(table))
    results = cur.fetchall()
    print(results)

or even using f-strings cur.execute(f"SELECT * FROM {table}" directly.

But what if there is malicious input in passed argument table like an appended DROP or TRUNCATE statement (SQL injection)?

Query building (safer)

Using SQL capable libraries (SQL framework or database-frontend) like psycopg, you can build the SQL using safe methods which apply input-validation.

See the examples in module psycopg2.sql to compose an SQL-statement for a given table parameter.

from psycopg2 import sql

cur.execute(
    sql.SQL("SELECT * FROM {} WHERE values IN (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])

---

Related Topics