On 6/11/18 4:23 AM, Olivier Mascia wrote:
>> Le 11 juin 2018 à 10:07, Peter Nacken <peter.nac...@gmx.de> a écrit :
>>
>> I try to insert email addresses into a table and get an error with addresses
>> they have a single quotation mark ( na'm...@domain.ltd ).
>> Sorry I forgot I‘m using C#
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it, you
> will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your
> data for inserting and it will be much better for SQL code injection
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for
> SQLite on how to prepare, bind, execute. Instead of building a final
> statement as a complete string and then execute it.
>
And for a simple example of why you want to prepare (or be very careful
how you sanitize your data) see https://xkcd.com/327/ (Bobby Tables).
You should NEVER just build an SQL statement (or any other sort of
executable statement) by just inserting data from some source into a SQL
string. Using prepare is the best, at a minimum you need to run it
through a quoting function appropriate for that language.
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
