Insert with an '

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Insert with an '

Peter Nacken
Hi,

I'm new in SQLite.

I try to insert email addresses into a table and get an error with addresses they have a single quotation mark ( na'[hidden email] ).

Sorry I'm facing this problem for weeks, I can't find a solution.

Is there a known workaround for it ?

Thanks for help

Peter
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

Peter Nacken
Sorry I forgot I‘m using C#

> Am 11.06.2018 um 10:07 schrieb Peter Nacken <[hidden email]>:
>
> Hi,
>
> I'm new in SQLite.
>
> I try to insert email addresses into a table and get an error with addresses they have a single quotation mark ( na'[hidden email] ).
>
> Sorry I'm facing this problem for weeks, I can't find a solution.
>
> Is there a known workaround for it ?
>
> Thanks for help
>
> Peter
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

Simon Slavin-3
In reply to this post by Peter Nacken


On 11 Jun 2018, at 9:07am, Peter Nacken <[hidden email]> wrote:

> I try to insert email addresses into a table and get an error with addresses they have a single quotation mark ( na'[hidden email] ).
>
> Sorry I'm facing this problem for weeks, I can't find a solution.

Every single ' character in a string needs to become two ' characters.  You can write a function which accepts one string and produces another with this change made.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

Olivier Mascia
In reply to this post by Peter Nacken
> Le 11 juin 2018 à 10:07, Peter Nacken <[hidden email]> a écrit :
>
> I try to insert email addresses into a table and get an error with addresses they have a single quotation mark ( na'[hidden email] ).

> 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''[hidden email]');
or
insert into T values("na'[hidden email]");

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.

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

Tim Streater-3
In reply to this post by Peter Nacken
On 11 Jun 2018, at 09:07, Peter Nacken <[hidden email]> wrote:

> I try to insert email addresses into a table and get an error with addresses
> they have a single quotation mark ( na'[hidden email] ).
>
> Sorry I'm facing this problem for weeks, I can't find a solution.
>
> Is there a known workaround for it ?

See:

<https://www.sqlite.org/faq.html>

and look at question 14 and its answer.



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

Peter Nacken
Dear all,

Thanks for your help. It works !

Peter

> Am 11.06.2018 um 11:14 schrieb Tim Streater <[hidden email]>:
>
>> On 11 Jun 2018, at 09:07, Peter Nacken <[hidden email]> wrote:
>>
>> I try to insert email addresses into a table and get an error with addresses
>> they have a single quotation mark ( na'[hidden email] ).
>>
>> Sorry I'm facing this problem for weeks, I can't find a solution.
>>
>> Is there a known workaround for it ?
>
> See:
>
> <https://www.sqlite.org/faq.html>
>
> and look at question 14 and its answer.
>
>
>
> --
> Cheers  --  Tim
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

Richard Damon
In reply to this post by Olivier Mascia
On 6/11/18 4:23 AM, Olivier Mascia wrote:

>> Le 11 juin 2018 à 10:07, Peter Nacken <[hidden email]> a écrit :
>>
>> I try to insert email addresses into a table and get an error with addresses they have a single quotation mark ( na'[hidden email] ).
>> 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''[hidden email]');
> or
> insert into T values("na'[hidden email]");
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Insert with an '

John McKown
In reply to this post by Olivier Mascia
Very good point. I think that everyone should do it that way. It is a bit
more work, but is vastly superior.

On Mon, Jun 11, 2018, 03:23 Olivier Mascia <[hidden email]> wrote:

> > Le 11 juin 2018 à 10:07, Peter Nacken <[hidden email]> a écrit :
> >
> > I try to insert email addresses into a table and get an error with
> addresses they have a single quotation mark ( na'[hidden email] ).
>
> > 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''[hidden email]');
> or
> insert into T values("na'[hidden email]");
>
> 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.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users