Mysql problem

Posted by Quit on Sun 07 Jun 2015 09:54 AM — 8 posts, 31,509 views.

Quit Sun 07 Jun 2015 09:54 AM #0

hi

When I run this in the Immediate window (ctrl + i)
it works without error.

MyQuestTarget = "a cat"
MyQuestRoom = "A Side Alley"
MyQuestArea = "The Land of the Beer Goblins"

assert (package.loadlib ("mysql.dll", "luaopen_luasql_mysql")) ()
	env = assert (luasql.mysql())
	con = assert (env:connect ("aardwolf", "", "", ""))
	cur = assert (con:execute ("SELECT * from lastkill WHERE room = '"..MyQuestRoom.."' AND zone = '"..MyQuestArea.."'"))
								
	row = cur:fetch ({}, "a")
	
	while row do

		print ("\n------ new row ---------\n")
                                print ("Name:", row.name)
		print ("Shortname:", row.shortname)
		
		-- table.foreach (row, print)  
		-- reusing the table of results
		row = cur:fetch (row, "a")
	end -- do
	
	-- close everything
	cur:close()
	con:close()
	env:close()

But if I put it in a alias like this:

<aliases>
  <alias
   match="testsql"
   enabled="y"
   send_to="12"
   sequence="100"
  >
  <send>MyQuestTarget = "a cat"
MyQuestRoom = "A Side Alley"
MyQuestArea = "The Land of the Beer Goblins"

assert (package.loadlib ("mysql.dll", "luaopen_luasql_mysql")) ()
env = assert (luasql.mysql())
con = assert (env:connect ("aardwolf", "", "", ""))
cur = assert (con:execute ("SELECT * from lastkill WHERE room = '"..MyQuestRoom.."' AND zone = '"..MyQuestArea.."'"))

row = cur:fetch ({}, "a")

while row do

print ("\n------ new row ---------\n")
print ("Name:", row.name)
print ("Shortname:", row.shortname)

-- table.foreach (row, print)  
-- reusing the table of results
row = cur:fetch (row, "a")
end -- do

-- close everything
cur:close()
con:close()
env:close()</send>
  </alias>
</aliases>

I get this error:

Compile error
World: Aardwolf
Immediate execution
[string "Alias: "]:14: unfinished string near '"'

Nick Gammon Australia Forum Administrator Sun 07 Jun 2015 09:38 PM #1


print ("\n------ new row ---------\n")

In an alias \n is turned into a newline, before the text hits the Lua interpreter. To make it work, double the number of backslashes:


print ("\\n------ new row ---------\\n")

Fiendish USA Global Moderator Sun 07 Jun 2015 10:30 PM #2

Quote:
In an alias \n is turned into a newline

Nitpick...

"\n" in a plugin XML block other than CDATA is turned into a newline by the XML plugin parser. A similar problem will happen with ">" or "<" (which you can replace with ">" and "<" respectively).
See: http://www.w3schools.com/xml/xml_cdata.asp

Another solution, therefore, is to assign a "script=" element to the alias and put your code there.

Nick Gammon Australia Forum Administrator Mon 08 Jun 2015 02:26 AM #3

That page does not mention backslashes.

The MUSHclient XML parser does not treat backslashes specially (otherwise it would already be a \\ by the time you copied it to the clipboard).

Can you quote a page that says that XML treats \n as a newline?

See this for example:

Quote:

If you want a new line character in an XML text node you can just put a new line character there. You don't need to escape it like that. So your unescaping code is also unnecessary.

In an XML element (which is what the <send> block is) a newline is literally itself. That is, you have a line break in the <send> block. You don't put \n there.

So if the XML parser sees \n it treats it as two characters, \ and n.

Nick Gammon Australia Forum Administrator Mon 08 Jun 2015 02:31 AM #4

However after that, in an alias "send" block, MUSHclient then converts \n to a newline. So thus in a send block if you had:


<aliases>
  <alias
   match="foo"
   enabled="y"
   sequence="100"
  >
  <send>say foo\nsay bar
</send>
  </alias>
</aliases>

It would treat that as:


say foo
say bar

You can test that and prove it works.

So if you want to say "foo\nbar" you would have to use this:


<aliases>
  <alias
   match="foo"
   enabled="y"
   sequence="100"
  >
  <send>say foo\\nsay bar
</send>
  </alias>
</aliases>

Now the alias "foo" gives this:


You say 'foo\nsay bar'

Nick Gammon Australia Forum Administrator Mon 08 Jun 2015 02:32 AM #5

And since Lua also treats \n as a newline, to put a backslash into Lua you have to double the backslashes again. For example, to say "\" you would do this:


<aliases>
  <alias
   match="foo"
   enabled="y"
   send_to="12"
   sequence="100"
  >
  <send>Send "say \\\\"</send>
  </alias>
</aliases>

Now we are up to 4 backslashes. The first two get past the MUSHclient treatment, the second two get past the Lua treatment.

You may find it easier to use script files rather than "send to script" if you use backslashes a lot.

Quit Tue 09 Jun 2015 04:23 PM #6

Thanks I think I got the backslash.

Can I use this conn:escape(str), I find it on this side:
http://keplerproject.github.io/luasql/doc/us/manual.html#mysql_extensions

I did try it whit this:

cur = assert (con:execute ("SELECT shortname from lastkill WHERE name = '"..con:escape(sqlname).."' AND zone = '"..con:escape(sqlzone).."'"))

but I get this error:

Run-time error
World: Aardwolf
Immediate execution
[string "Immediate"]:8: attempt to call method 'escape' (a nil value)
stack traceback:
        [string "Immediate"]:8: in function 'getmydata'
        [string "Immediate"]:34: in main chunk

Its run from the Immediate window

Nick Gammon Australia Forum Administrator Tue 09 Jun 2015 08:33 PM #7

If you want to fixup strings for use inside SQL statements you can use fixsql which I wrote a while back for my own purposes:


mysql_replacements = { 
   ["\0"] = "\\0",
   ["\n"] = "\\n",
   ["\r"] = "\\r",
   ["\'"] = "\\\'",
   ["\""] = "\\\"",
   ["\026"] = "\\Z",
   ["\b"] = "\\b",
   ["\t"] = "\\t",
   ["\\"] = "\\\\",
   }

-- Fix SQL text by converting various characters to the format MySQL 
--  will recognise in its string processor
--
-- Note that not all the escapes are necessary for internal SQL use, 
-- however if data is being dumped to disk (eg. as SQL statements) 
-- then it is handy for have things like \n and \r made more readable
--   See: http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html

function fixsql (s, maxSize, notNull)
  
  if s == nil then
    -- NULL not permitted so return an empty string
    if notNull then
      return "''"
    end -- if
    return "NULL"
  end -- if nil
  
  if string.match (s, "^%d+$") then
    return s
  end -- if number
  
  if type (s) == "string" then
    if Trim (s) == "" and not notNull then
      return "NULL"
    end -- if empty
    s = string.gsub (s, "<br/>", "\n")
    if maxSize then
      assert (#s <= maxSize, "String '" .. s .. "' exceeds maximum size of " .. maxSize)
    end -- if
  end -- if
  
  return ("'" .. string.gsub (tostring (s), "[%z\n\r\'\"\026\b\t\\]", 
    function (str)
      return mysql_replacements [str] or str
    end ) .. "'")

end -- fixsql

That also turns nil into NULL. For this to work it quotes the string if required (otherwise you would get "NULL" and not NULL) so don't put the quotes in your SQL. For example:


cur = assert (con:execute ("SELECT shortname from lastkill WHERE name = " .. 
              fixsql(sqlname) ..
              " AND zone = " .. 
              fixsql(sqlzone)))

There is also a maxSize argument (you can leave it as nil) to check that strings are not too long. I happened to need that in what I was working on, to make sure that you didn't try to put (say) 100 characters into a VARCHAR(20).