RPAD/LPAD

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

RPAD/LPAD

Walter Hurry
A small enhancement request:

It would be great if the RPAD and LPAD functions could be implemented in sqlite.

I know I can easily achieve the equivalent by concatenating and TRUNCing, but if the functions were available natively it would avoid the need to hack third party SQL scripts.

Dr. Hipp?

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

Re: RPAD/LPAD

Richard Hipp-3
On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry <[hidden email]> wrote:

> A small enhancement request:
>
> It would be great if the RPAD and LPAD functions could be implemented in
> sqlite.
>

The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.


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

Re: RPAD/LPAD

Walter Hurry
Richard Hipp wrote:

> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry <[hidden email]> wrote:
>
>> A small enhancement request:
>>
>> It would be great if the RPAD and LPAD functions could be implemented in
>> sqlite.
>>
>
> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
> See http://www.sqlite.org/lang_corefunc.html#printf for details.

Thanks, but you snipped the relevant part of my post:

"I know I can easily achieve the equivalent ... but if the functions were available natively it would avoid the need to hack third party SQL scripts."

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

Re: RPAD/LPAD

Gabor Grothendieck
On Thu, Mar 6, 2014 at 6:29 PM, Walter Hurry <[hidden email]> wrote:

> Richard Hipp wrote:
>
>> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry <[hidden email]> wrote:
>>
>>> A small enhancement request:
>>>
>>> It would be great if the RPAD and LPAD functions could be implemented in
>>> sqlite.
>>>
>>
>> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
>> See http://www.sqlite.org/lang_corefunc.html#printf for details.
>
> Thanks, but you snipped the relevant part of my post:
>
> "I know I can easily achieve the equivalent ... but if the functions were available natively it would avoid the need to hack third party SQL scripts."
>

I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different.  Its not just
rpad and lpad but other functions too.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

R Smith

On 2014/03/07 01:59, Gabor Grothendieck wrote:

>
>>
>>>
>>>> A small enhancement request:
>>>>
>>>> It would be great if the RPAD and LPAD functions could be implemented in
>>>> sqlite.
>>>>
>>> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
>>> See http://www.sqlite.org/lang_corefunc.html#printf for details.
>> Thanks, but you snipped the relevant part of my post:
>> "I know I can easily achieve the equivalent ... but if the functions were available natively it would avoid the need to hack third party SQL scripts."
>>
> I have also found that it was tedious to retarget MySQL scripts to
> SQLite because many of the function calls are different.  Its not just
> rpad and lpad but other functions too.

Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I agree, it's a bit of a chore to retarget SQL scripts to
SQLite sometimes, but not really moreso than retargeting a script from MSSQL to PostGres or Oracle, or PostGres to MySQL (which btw.
doesn't support WITH RECURSIVE at all) etc.[1]   To single out the SQLite differences as anything more than standard incompatibility
between SQL engines would be... exaggerative. (I just made up that word!) - And still with the bit of incompatibility we need to
deal with for a world of clever, trusted and fast querying in an engine that is a fraction the size of anything else, so much so
that you can run it on a smart calculator....  hardly a deficit.

Add to this the fact that you can - through SQL's ability to add user-defined functions (an almost unique ability among SQL engines)
- add your own RPAD and LPAD functions that work exactly how you envision... you have the power to solve your own problem.

[1]: Yes I know MySQL has other ways to deal with recursion, but the point is that revising the scripts will need a LOT of
adjustment to achieve the same, which is the point of this note.


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

Re: RPAD/LPAD

Gabor Grothendieck
On Thu, Mar 6, 2014 at 8:41 PM, RSmith <[hidden email]> wrote:

>
> On 2014/03/07 01:59, Gabor Grothendieck wrote:
>>
>>
>>>
>>>>
>>>>> A small enhancement request:
>>>>>
>>>>> It would be great if the RPAD and LPAD functions could be implemented
>>>>> in
>>>>> sqlite.
>>>>>
>>>> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
>>>> See http://www.sqlite.org/lang_corefunc.html#printf for details.
>>>
>>> Thanks, but you snipped the relevant part of my post:
>>> "I know I can easily achieve the equivalent ... but if the functions were
>>> available natively it would avoid the need to hack third party SQL scripts."
>>>
>> I have also found that it was tedious to retarget MySQL scripts to
>> SQLite because many of the function calls are different.  Its not just
>> rpad and lpad but other functions too.
>
>
> Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I
> agree, it's a bit of a chore to retarget SQL scripts to SQLite sometimes,
> but not really moreso than retargeting a script from MSSQL to PostGres or

I have also retargeted MySQL scripts to H2 and it was easier than to SQLite.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

Zsbán Ambrus
In reply to this post by R Smith
On 3/7/14, RSmith <[hidden email]> wrote:
> Add to this the fact that you can - through SQL's ability to add
> user-defined functions (an almost unique ability among SQL engines)

Is that really so?  I thought most sql engines had that.  You can
define functions in at least postgreSQL:

    http://www.postgresql.org/docs/9.3/interactive/xfunc.html

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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 7:38 AM, Zsbán Ambrus <[hidden email]> wrote:
> On 3/7/14, RSmith <[hidden email]> wrote:
>> Add to this the fact that you can - through SQL's ability to add
>> user-defined functions (an almost unique ability among SQL engines)
>
> Is that really so?  I thought most sql engines had that.  You can
> define functions in at least postgreSQL:
>     http://www.postgresql.org/docs/9.3/interactive/xfunc.html

You can only define custom SQL function in C in SQLite.

The SQLite core lacks a built-in "scripty" engine which would allow it
to define new SQL function in "SQL/scripty", unlike most
bigger/heavier RDBMSs.

I think what SQLite lacks is a syntax to define custom function like
it does for virtual tables. Something like:

create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";

This above statement would have SQLite call some API on scripty_module
in charge of registering a function_pointer+userdata+userdatadelfunc
using the usual SQLite api to register custom functions, and
scripty_module will have to "compile" or "interpret" the function's
"code" (e.g. "return PRINTF('%-*s',y,x)") and run it with the supplied
sqlite3_value[] it receives (including unpacking the args to the
correct type the scripty function expects).

extension modules can also add custom functions, but a fixed set of
hard-coded functions. It could of course export a function that does
the equivalent of the "create function" SQL I dreamed up/showed above,
but then there's no standard/unified way to write custom scripty
functions, because of course we'd have a scripty_module to write
functions in js/lua/python/ruby/tcl/etc...

The SQLite core team extensively uses TCL already, so they could do a
proof-of-concept with TCL.

Most scripting languages wrap SQLite inside, but here I'm talking
about the reverse, it would be SQLite wrapping the scripting language
to SQL extensions, usable from the command-line sqlite3 familiar tool.
Again, it's already possibly now via extension module, but an official
syntax for scripted extension SQL functions, with readily available
such modules would improve the SQLite ecosystem IMHO.

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

Re: RPAD/LPAD

Eduardo Morras-2
In reply to this post by Gabor Grothendieck
On Thu, 6 Mar 2014 22:41:31 -0500
Gabor Grothendieck <[hidden email]> wrote:

> On Thu, Mar 6, 2014 at 8:41 PM, RSmith <[hidden email]> wrote:
> >
> > On 2014/03/07 01:59, Gabor Grothendieck wrote:
> >>
> >>
> >>>
> >>>>
> >>>>> A small enhancement request:
> >>>>>
> >>>>> It would be great if the RPAD and LPAD functions could be
> >>>>> implemented in
> >>>>> sqlite.
> >>>>>
> >>>> The SQLite you can get the effect of RPAD(x,y) using PRINTF
> >>>> ('%-*s',y,x). See
> >>>> http://www.sqlite.org/lang_corefunc.html#printf for details.
> >>>
> >>> Thanks, but you snipped the relevant part of my post:
> >>> "I know I can easily achieve the equivalent ... but if the
> >>> functions were available natively it would avoid the need to hack
> >>> third party SQL scripts."
> >>>
> >> I have also found that it was tedious to retarget MySQL scripts to
> >> SQLite because many of the function calls are different.  Its not
> >> just rpad and lpad but other functions too.
> >
> >
> > Speaking as someone who retargets (nice word btw.) SQL scripts
> > often, yes I agree, it's a bit of a chore to retarget SQL scripts
> > to SQLite sometimes, but not really moreso than retargeting a
> > script from MSSQL to PostGres or
>
> I have also retargeted MySQL scripts to H2 and it was easier than to
> SQLite.

Creating extensions in SQLite is not difficult nor hard. You can define yours with this line:

 sqlite3_create_function(db, "RPAD", SQLITE_UTF8, SQLITE_ANY||SQLITE_DETERMINISTIC, 0, sqlcmd_rpad, 0, 0);

And create your function with:

 static void sqlcmd_rpad(sqlite3_context *context, int argc,sqlite3_value **argv){
   
   // argc has number of parameters in **argv
   // parse them as you do within C main
   //
   sqlite3_result_text(context, char_to_return, length_char_to_return, SQLITE_TRANSIENT);
 }

Some weeks ago, there was a mail collecting several sites where find usefule functions && extensions.

HTH

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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 10:07 AM, Eduardo Morras <[hidden email]> wrote:
> Creating extensions in SQLite is not difficult nor hard. You can define yours with this line:

It's no hard, no, if you're already a C developer. But it's hardly
convenient when you need something quickly.

Between creating a new C file, coding it, compiling it, packaging it
as a DLL/SO, .load it, test it, repeat on errors/crashes, and using
straight sqlite3 CLI with a downloaded scripty_module extension and
using direct trial-and-error at a REPL using already known "scripty"
language, that's night and day IMHO. Plus the scripty solution will
typically be cross-platform, easily packaged in a .sql one can .read
(which also does the necessary .load, acting as scripted function
"libraries"), as long as the generic scrypty_module is available for
that platform. Going down the C route just easiest a viable option to
many, and even for those who can do it in C, the scripty route is more
productive to prototype, since immediate and interactive at the SQLite
REPL. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

Max Vlasov
In reply to this post by Dominique Devienne
On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <[hidden email]> wrote:
> I think what SQLite lacks is a syntax to define custom function like
> it does for virtual tables. Something like:
>
> create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";
>

Nice suggestion. This probably falls into case when a small new part
needed on sqlite side and large DSL specific features depending on the
side of the registered module. But you still needs some binary module
for your functions to work

How about "macro expression substitution" feature? Something that
could accept any expression compatible with sqlite as the function
body to be just translated into corresponding vdbe instructions.

For example,

Create function complexcase(x,y) as Case when x < 10 then y else y + 10 end
....
Select complexcase(myField1, myField*10) from t;

On sqlite site, the engine can only syntax check for the create
function body, but the referencing it in actual query would require
actual substitution with the corresponding context-sensitive errors.

This definitely will be more constly in kB on sqlite side than module
approach, but would create a more universal solution.

Just quick thoughts, probably there are plenty of obstacles here :)

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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 11:29 AM, Max Vlasov <[hidden email]> wrote:

> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <[hidden email]> wrote:
>> I think what SQLite lacks is a syntax to define custom function like
>> it does for virtual tables. Something like:
>>
>> create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";
>>
>
> Nice suggestion. This probably falls into case when a small new part
> needed on sqlite side and large DSL specific features depending on the
> side of the registered module. But you still needs some binary module
> for your functions to work.

[DD] Right. But I'd expect such modules to appear for
tcl/lua/python/ruby quickly. There's not much glue code needed on the
module side, basically unpacking the sqlite3_value into an equivalent
scripty value, and eval the script, converting back the resulting
scripty value into a sqlite3_value via result context, as usual for C
extension function.

> How about "macro expression substitution" feature? Something that
> could accept any expression compatible with sqlite as the function
> body to be just translated into corresponding vdbe instructions.

I did think of a "native" SQLite "scripting", but didn't think of
using SQL expressions SQLite already support. Very interesting.

> For example,
>
> Create function complexcase(x,y) as Case when x < 10 then y else y + 10 end
> ....
> Select complexcase(myField1, myField*10) from t;
>
> On sqlite site, the engine can only syntax check for the create
> function body, but the referencing it in actual query would require
> actual substitution with the corresponding context-sensitive errors.

That's the difficult part probably. And it's still compatible with
using create function ... using module syntax. w/o "using module" you
are using the implicit and native SQL-based syntax as the "scripty"
part.

> This definitely will be more constly in kB on sqlite side than module
> approach, but would create a more universal solution.
>
> Just quick thoughts, probably there are plenty of obstacles here :)

Which makes it more likely not to happen unfortunately. Whereas a
simple create function func(args...) using module as
module_specific_source_code would require much less code in SQLite
core, and just a sqlite3_module_v2 that extends sqlite3_module with
two new callbacks for scalar and aggregate functions registrations,
with bump of sqlite3_module.iVersion for BC. Ideally SQLite would gain
a pragma to list functions, something I've long wished for. More
wishful thinking basically :) --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

Clemens Ladisch
In reply to this post by Max Vlasov
Max Vlasov wrote:
> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <[hidden email]> wrote:
>> I think what SQLite lacks is a syntax to define custom function like
>> it does for virtual tables. Something like:
>>
>> create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";
>
> Nice suggestion. This probably falls into case when a small new part
> needed on sqlite side

Actually, no change to SQLite itself would be needed.  It's possible
to create an extension that provides a function that allows to register
another function that executes a custom SQL expression:

 SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');


In practice, the biggest problem probably is that SQLite doesn't have
that many built-in functions; most useful functions would require more
than that.


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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 3:39 PM, Clemens Ladisch <[hidden email]> wrote:

> Max Vlasov wrote:
>> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <[hidden email]> wrote:
>>> I think what SQLite lacks is a syntax to define custom function like
>>> it does for virtual tables. Something like:
>>>
>>> create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";
>>
>> Nice suggestion. This probably falls into case when a small new part
>> needed on sqlite side
>
> Actually, no change to SQLite itself would be needed.  It's possible
> to create an extension that provides a function that allows to register
> another function that executes a custom SQL expression:
>
>  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');

I've already mentioned that Clemens. But also pointed about that then
you don't have a standard way to register those dynamically generated
functions. FWIW. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

Eleytherios Stamatogiannakis
IMHO, SQLite is targeted towards being a relational "core" with very
wide extensibility. Adding specific scripting engines to it is would be
detrimental to its main purpose (being a very good relational "core").

In our group, we use SQLite + UDFs written in Python. Creating new
functions is very simple (most of the functions are a couple Python
lines). The API of SQLite permits very advanced functionality to be tied
to it (infinite streaming virtual tables, indexes written in Python,
etc). And (in our benchmarks) the speed is better than Postgres and a
LOT better than MySQL.

Finally, the simplicity and extensibility of SQLite has permitted us to
create hundreds of functions in a very short time [*].

l.

[*] http://doc.madis.googlecode.com/hg/index.html

On 07/03/14 16:59, Dominique Devienne wrote:

> On Fri, Mar 7, 2014 at 3:39 PM, Clemens Ladisch <[hidden email]> wrote:
>> Max Vlasov wrote:
>>> On Fri, Mar 7, 2014 at 12:49 PM, Dominique Devienne <[hidden email]> wrote:
>>>> I think what SQLite lacks is a syntax to define custom function like
>>>> it does for virtual tables. Something like:
>>>>
>>>> create function rpad(x, y) using scripty_module as "return PRINTF('%-*s',y,x)";
>>>
>>> Nice suggestion. This probably falls into case when a small new part
>>> needed on sqlite side
>>
>> Actually, no change to SQLite itself would be needed.  It's possible
>> to create an extension that provides a function that allows to register
>> another function that executes a custom SQL expression:
>>
>>   SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>
> I've already mentioned that Clemens. But also pointed about that then
> you don't have a standard way to register those dynamically generated
> functions. FWIW. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 5:27 PM, Eleytherios Stamatogiannakis
<[hidden email]> wrote:
> IMHO, SQLite is targeted towards being a relational "core" with very wide
> extensibility. Adding specific scripting engines to it is would be
> detrimental to its main purpose (being a very good relational "core").

You are misrepresentation the discussions in this thread IMHO.

We never discussed adding a scripting engine to SQLite, but making it
easy to supercharge SQLite with the ability to write extension
function via scripting (in the broad sense) instead of via compiled C
extensions. And we proposed a syntax to declare those scripted custom
function in a way that's fully compatible and consistent with the way
virtual tables are declared.

And once again, all of the above is already possible, via compiled
extension modules, as Clemens pointed out, and you also pointed out by
mentioning your own Python-implemented custom functions. But you had
to write custom C code to be able to have SQLite know about and use
your Python-implemented custom functions, and you likely embed SQLite
inside your Python environment, while I propose the reverse, and a
standard syntax to register those functions, which is extension-module
and thus scripting-engine independent.

In any case, Dr. Hipp does not participate in this thread, so it will
likely stay wishing thinking :). I still think my proposal is elegant,
SQLite-esque, useful, and importantly doable; and lightweight in code
size to boot. But of course documenting and testing this, to SQLite's
high standards, that's definitely work. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

Max Vlasov
In reply to this post by Clemens Ladisch
On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch <[hidden email]> wrote:

> Max Vlasov wrote:
>>
>> Nice suggestion. This probably falls into case when a small new part
>> needed on sqlite side
>
> Actually, no change to SQLite itself would be needed.  It's possible
> to create an extension that provides a function that allows to register
> another function that executes a custom SQL expression:
>
>  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>
>

Brilliant :) this is a solution not requiring adding anything to
sqlite. I'd only prefer not using full-features sql queries, but
rather expression that is automatically wrapped in SELECT without
FROM, otherwise they would create dimensions (columns, rows) when our
function strictly expected one result. And also support for numbered
parameters. So my example whould be implemented as

SELECT register_function('complexcase', 2, 'Case when ?1 < 10 then ?2
else ?2 + 10 end');

It's interesting that such approach makes this super-function
Pragma-like. It changes some internal state (reveals a new function)
and also optionally returns some result, for example 1 for success.

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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 7:37 PM, Max Vlasov <[hidden email]> wrote:

> On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch <[hidden email]> wrote:
>> Max Vlasov wrote:
>>>
>>> Nice suggestion. This probably falls into case when a small new part
>>> needed on sqlite side
>>
>> Actually, no change to SQLite itself would be needed.  It's possible
>> to create an extension that provides a function that allows to register
>> another function that executes a custom SQL expression:
>>
>>  SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>>
>>
>
> Brilliant :) this is a solution not requiring adding anything to
> sqlite. I'd only prefer not using full-features sql queries, but
> rather expression that is automatically wrapped in SELECT without
> FROM, otherwise they would create dimensions (columns, rows) when our
> function strictly expected one result. And also support for numbered
> parameters. So my example whould be implemented as
>
> SELECT register_function('complexcase', 2, 'Case when ?1 < 10 then ?2
> else ?2 + 10 end');
>
> It's interesting that such approach makes this super-function
> Pragma-like. It changes some internal state (reveals a new function)
> and also optionally returns some result, for example 1 for success.

OK, reading Max's answer, I realize I misunderstood your original post
Clemens. Sorry about that.

But doesn't the above assume you can bind values inside the select
clause? I thought one couldn't...

The new CTE might work-around that, if that's really the case, since
hopefully it should be possible to use bind variables in the first
statement below, while somehow I thought one couldn't in the second
statement.

sqlite> with args(x, y) as (VALUES('foo', 16)) select printf('%-*s',
y, x) from args;
foo
sqlite> select printf('%-*s', y, x) from (select 'foo' as x, 16 as y);
foo
sqlite>

basically register_function('rpad', 'x', 'y', 'printf(''%-*s'', y,
x)') would register a 2-arg function (register_function's argc-2)
named $argv[0], which executes the following statement

with args($argv[1],  $argv[2], ... $argv[argc-2]) as (VALUES(?, ?))
select $argv[argc-1) from args;

with $argv[i] textually replaced. On executing the registered rpad
function, one would forward the args to the inner statement prepared
above via sqlite3_bind_value, and execute it.

Of course, during inner query preparation, one can verify the
expression returns a single scalar value, and at execution time, that
it returns a single row (it can't I think, but I'm not experienced in
SQL injection techniques, so I'd assert it myself ;) I think I might
try to code such a super-function inside our app.

I agree with Max. Brilliant! --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: RPAD/LPAD

Eduardo Morras-2
In reply to this post by Clemens Ladisch
On Fri, 07 Mar 2014 15:39:57 +0100
Clemens Ladisch <[hidden email]> wrote:

> Actually, no change to SQLite itself would be needed.  It's possible
> to create an extension that provides a function that allows to
> register another function that executes a custom SQL expression:
>
>  SELECT register_simple_function('rpad', 2, 'SELECT printf
> (''%-*s'', ?, ?)');
>
>
> In practice, the biggest problem probably is that SQLite doesn't have
> that many built-in functions; most useful functions would require more
> than that.

So, if a webapp that uses SQLite doesn't check it's input, functions that renames SQLite internals can be injected

SELECT register_simple_function('MAX', 1, 'DROP TABLE ?');

No?

>
>
> Regards,
> Clemens

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

Re: RPAD/LPAD

Dominique Devienne
On Fri, Mar 7, 2014 at 10:19 PM, Eduardo Morras <[hidden email]> wrote:

> On Fri, 07 Mar 2014 15:39:57 +0100
> Clemens Ladisch <[hidden email]> wrote:
>
>> Actually, no change to SQLite itself would be needed.  It's possible
>> to create an extension that provides a function that allows to
>> register another function that executes a custom SQL expression:
> So, if a webapp that uses SQLite doesn't check it's input, functions that renames SQLite internals can be injected
>
> SELECT register_simple_function('MAX', 1, 'DROP TABLE ?');
>
> No?

Not of the select is implicit, because then "select drop table ?" is invalid.

See my previous post. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
12