Can I run HTTP GET directly in SQL under MySQL?

I would like to do this:

UPDATE table SET blobCol = HTTPGET(urlCol) WHERE whatever LIMIT n;

      

Is there any code for this? I knew this should be possible as the MySQL Docs includes an example of adding a function that does DNS lookups.

MySQL / windows / Preferably no compilation, but I can.

(If you haven't heard anything like it, but you'd expect it to exist, then "nice" would be nice.)

EDIT: I knew this would open up a whole protection against o-worms, but in my cases the only DB access is through the mysql console application. It is not a globally available system. This is not the end of the web. This is only a local data logging system

+1


source to share


4 answers


No, thank God, that would be a security horror. Every SQL injection hole in the application can be used to launch spam connections to attack other sites.



You could, I suppose, write it in C and compile it as a UDF. But I don't think it really gives you anything compared to just SELECTing in your application layer and looping the results doing HTTP GET and UPDATE. If we're talking about making HTTP connections, the added efficiency of doing this in the database layer will be completely overshadowed by network latency.

+5


source


I don't know of any function like this as part of MySQL. Are you just trying to get HTML data from many urls?

An alternative solution could be to use the importhtml function of the google spreadsheet.



Google Spreadsheets lets you import data online

+2


source


Don't do it. Best practice in a web environment is that the database servers are isolated from the outside, in both directions, which means the db server will not be allowed to fetch data from the internet.

+1


source


Don't do it.

If you are absolutely sure that you want to receive web content from the SQL environment, then as far as I know there are two possibilities:

  • Write a custom MySQL UDF in C (as bobince mentioned). This could potentially potentially be a huge amount of work, depending on your C experience, how much security you want, how much you want the UDF to be like this: eg. Just GET requests? How about POST? CHAPTER? and etc.

  • Please use another database that can do this. If you are happy with SQL, perhaps you can do it with PostgreSQL and one of the equipped languages ​​like Python or PHP.

If you're not too fussy about sticking to SQL, you can use something like eXist . You can do this quite easily with XQuery and could easily modify the results to fit your schema (rather than just insert them into the blob field) or save the page as is as an xhtml document in the DB.

Then you can quickly run queries across all documents, like get all links or quotes or whatever. You can even apply XSL to such a result with very little extra work. Great if you are storing pages for reference and want to tailor the results into a personal intranet application.

In addition, because eXist is document oriented, it has many great methods for fuzzy text search, close word search, and has an excellent full text index (much better than MySQL). Ideal if you are after doing some data mining on the content, for example: find me all the documents where the word "burger" is in 50 words of "hot dog" where the word is not on the UL list. Try it in MySQL!

Aside, and without malice; I often wonder why eXist is being revisited when people are building a CMS. Its database, which can store content in its native format (XML or a subset of it (x) HTML), easily queries it in its native format, and can translate it from its own format using a powerful templating language that looks and acts like its own format. Sometimes SQL is just wrong for the job!

Unfortunately. Doesn't mean waffles!: - $

0


source







All Articles