Is there a way to URL encode fields in SQLite directly in the query?
(somehow this question doesn't meet "quality standards" so I'll throw in an example query:)
select urlencode( field ) from table;
-
What exactly are you expecting that to do?David Schwartz– David Schwartz2012-06-28 06:06:55 +00:00Commented Jun 28, 2012 at 6:06
-
This is not the job of the database really. Let alone the database as simple as SQLite. Pretty sure you would have to do that in the code.Maxim Sloyko– Maxim Sloyko2012-06-28 06:09:11 +00:00Commented Jun 28, 2012 at 6:09
-
1@DavidSchwartz Specifically, I am trying to query an SQLite database created by Google Chrome containing a list of usernames and passwords (see thebigbrowser.blogspot.com/2012/04/…) and export it to a CSV file that I can then use to import into Firefox using this add-on addons.mozilla.org/en-US/firefox/addon/password-exporter however the fields need to be url encoded.Mike– Mike2012-06-28 20:52:19 +00:00Commented Jun 28, 2012 at 20:52
Add a comment
|
3 Answers
Looking through the sqlite function list, I didn't find anything that would do the job. But you can construct such a function manually as follows:
select replace(replace(replace(replace(field,
" ", "%20"),
"/", "%2F"),
"(", "%28"),
")", "%29")
from table;
This version escapes four of the more common problematic characters. It's a quick-and-dirty solution if your data set does not contain many unsafe characters.
Comments
SQLite does not support URL encoding out of the box.
But you can add URL encoding/decoding functions with the sqlean-crypto extension.
For example:
select load_extension('crypto.dll');
select encode('/search?q=sql+is+awesome', 'url');
-- %2Fsearch%3Fq%3Dsql%2Bis%2Bawesome
select decode('%2Fsearch%3Fq%3Dsql%2Bis%2Bawesome', 'url');
-- /search?q=sql+is+awesome
The extension also supports other encoding algorithms like Base32, Base64 and Base85.
Comments
If the target is web browser, you can use javascript to achieve that like below:
select '<a href=# onclick=''window.open("https://api.whatsapp.com/send?text="+encodeURIComponent(" Welcome '||CustomerName||'"), "_blank").focus();''>wtsup</a>' wtsup2 from customer_table ;
1 Comment
Mike
I wouldn't recommend doing that. You should never output user data directly to the browser without properly escaping it first. You're just asking for an XSS attack.