Register forum user name Search FAQ

Gammon Forum

Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the password reset link.

Due to spam on this forum, all posts now need moderator approval.

 Entire forum ➜ MUSHclient ➜ General ➜ Mysql problem

Mysql problem

It is now over 60 days since the last post. This thread is closed.     Refresh page


Posted by Quit   (16 posts)  Bio
Date Sun 07 Jun 2015 09:54 AM (UTC)

Amended on Sun 07 Jun 2015 09:57 AM (UTC) by Quit

Message
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 '"'
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #1 on Sun 07 Jun 2015 09:38 PM (UTC)
Message

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")

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Fiendish   USA  (2,534 posts)  Bio   Global Moderator
Date Reply #2 on Sun 07 Jun 2015 10:30 PM (UTC)

Amended on Sun 07 Jun 2015 10:32 PM (UTC) by Fiendish

Message
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 "&gt;" and "&lt;" 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.

https://github.com/fiendish/aardwolfclientpackage
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #3 on Mon 08 Jun 2015 02:26 AM (UTC)

Amended on Mon 08 Jun 2015 02:27 AM (UTC) by Nick Gammon

Message
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

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #4 on Mon 08 Jun 2015 02:31 AM (UTC)
Message
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

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #5 on Mon 08 Jun 2015 02:32 AM (UTC)

Amended on Mon 08 Jun 2015 02:34 AM (UTC) by Nick Gammon

Message
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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Quit   (16 posts)  Bio
Date Reply #6 on Tue 09 Jun 2015 04:23 PM (UTC)
Message
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
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #7 on Tue 09 Jun 2015 08:33 PM (UTC)
Message
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).

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


22,707 views.

It is now over 60 days since the last post. This thread is closed.     Refresh page

Go to topic:           Search the forum


[Go to top] top

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.