Edit

Share via

Parameters

  • Article

Parameters are used to protect against SQL injection attacks. Instead of concatenating user input with SQL statements, use parameters to ensure input is only ever treated as a literal value and never executed. In SQLite, parameters are typically allowed anywhere a literal is allowed in SQL statements.

Parameters can be prefixed with either :, @, or $.

command.CommandText =
@"
    INSERT INTO user (name)
    VALUES ($name)
";
command.Parameters.AddWithValue("$name", name);

See Data types for details about how .NET values are mapped to SQLite values.

Truncation

Use the Size property to truncate TEXT and BLOB values.

// Truncate name to 30 characters
command.Parameters.AddWithValue("$name", name).Size = 30;

Alternative types

Sometimes, you may want to use an alternative SQLite type. Do this by setting the SqliteType property.

The following alternative type mappings can be used. For the default mappings, see Data types.

Value SqliteType Remarks
Char Integer UTF-16
DateOnly Real Julian day value
DateTime Real Julian day value
DateTimeOffset Real Julian day value
Guid Blob
TimeOnly Real In days
TimeSpan Real In days 
command.CommandText =
@"
    SELECT count(*)
    FROM task
    WHERE finished IS NULL
        AND julianday('now') - julianday(started) > $expected
";
// Convert TimeSpan to days instead of text
command.Parameters.AddWithValue("$expected", expected).SqliteType = SqliteType.Real;

Output parameters

SQLite doesn't support output parameters. Return values in the query results instead.

See also