Jump to content
UBot Underground

Recommended Posts

I spent the last couple of days figuring out how to scrape articles and drop them directly into my database and I thought that I would share what I have learned.

 

I'm going to assume that you have an instance of mysql up and running either locally or on a server somewhere and that you can connect to it.

 

I have a very basic setup with two tables, "articles" and "keywords"

 

The articles table has "ID, keyword, title, article" as columns and the keywords table has "ID, keyword'

 

I have prepopulated the keywords table with several thousand keywords to pull from

 

First up - query the database for the keyword and put it into a variable

 

plugin command("DatabaseCommands.dll", "connect to database", "server=localhost;uid=****; pwd=****; database=articles; port=3306; pooling=false") {
 plugin command("DatabaseCommands.dll", "query with results", "SELECT keyword from keywords order by rand() limit 1", #keyword)
}

 

 

The trick to getting data back into the database is that you need to escape your input. In other words you need to change ' to \' and " to \"

 

This took me a bit to figure out the syntax so here it is:

 

set(#article, $replace(#article, "'", "\\\'"), "Global")
set(#article, $replace(#article, "\"", "\\\""), "Global")

 

notice that it takes three \\\ in order to get one \ on the output - this has to do with special characters (you also need to escape the " in the replace line in order for the system to be able to find the " because both " and \ are special characters)

 

Finally, to insert into the database - you need to use this format {#keyword}

 

plugin command("DatabaseCommands.dll", "connect to database", "server=65.39.148.178;uid=writer; pwd=owgn2wby;database=articles; port=3306; pooling=false") {
 plugin command("DatabaseCommands.dll", "query","INSERT INTO articles (keywordID,title,article) VALUES ('{#keyword}','{#title}','{#article}');")
}

 

hope this helps somebody out in the future

  • Like 4
Link to post
Share on other sites

First up - query the database for the keyword and put it into a variable

 

plugin command("DatabaseCommands.dll", "connect to database", "server=localhost;uid=****; pwd=****; database=articles; port=3306; pooling=false") {
 plugin command("DatabaseCommands.dll", "query with results", "SELECT keyword from keywords order by rand() limit 1", #keyword)
}

 

I'm a bit puzzled... My UBot Studio only accepts tables as results containers in the "query with results" node... Looks like you used a variable there (#keyword)

I'm wondering how have you done that?

 

Personally, I am using a temporary table with a single cell, but that is only an artifice, to be able to have things done quickly, w/o asking Eddie to code extra features yet (although it would be nice, Eddie...)

 

As a side note, in the code you put above, I would edit out the password if I were you... just for the sake of being over-cautious, but still...

 

--------

 

In regards to the escaping of characters, for people using ANY SQL editor... once they built their query, they could drop it in Notepad++ and have everything escaped automatically (under the TestFX Characters TAB/Option there)

 

HTH...

  • Like 1
Link to post
Share on other sites

I agree VB. He is using that variable in error. Will not work in the manner that he is using it. If it does then it is a fluke. I requested that they change this back during our Beta testing but it as not been changed yet.

  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...