Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MySQL & Symbols?
Author Message
Llirik
Journeyman
*

Posts: 115
Likes Given: 0
Likes Received: 10 in 8 posts
Joined: Feb 2015
Reputation: 0

UO Forum

Post: #1
MySQL & Symbols?
How block symbols: \, " and other in DTEXTENTRY & ")" symbol. Because MySQL give error with this symbols:

[FUNCTION email]
if (<tag0.email> == 0)
tag0.changing_npc_text=Enter Your e-mail address to restore the password and more on Your character.
else
tag0.changing_npc_text=Your e-mail address: <tag0.e-mail>. You can change it.
endif
sdialog d_mailchange
return 1

[DIALOG d_mailchange]
20,50
NOMOVE
NOCLOSE
RESIZEPIC 0 0 3500 350 125
RESIZEPIC 20 83 3000 250 25
BUTTON 274 87 1154 1155 1 0 1
DHTMLGUMP 18 16 316 59 1 1 <src.tag0.changing_npc_text>
if (<src.tag0.email> == 0)
DTEXTENTRY 25 83 235 25 1161 0
else
DTEXTENTRY 25 83 235 25 1161 0 <src.tag0.e-mail>
BUTTON 306 87 1151 1152 1 0 2
endif

[DIALOG d_mailchange BUTTON]
ONBUTTON=1
if (STRMATCH("*@*", "<argtxt[0]>")) && !(STRMATCH("*"*", "<argtxt[0]>")) && !(STRMATCH("*\*", "<argtxt[0]>")) // Symbol ")" not deleted.
for x 0 <eval <serv.accounts>-1>
for y 0 <eval <serv.MaxCharsPerAccount>-1>
ref1 = <serv.account.<dlocal.x>.char.<dlocal.y>>
if (STRMATCH("<argtxt[0]>", "<ref1.tag0.e-mail>")) && (<src>!=<ref1>)
src.sysmessage @0487,,1 Этот E-mail адрес уже используется!
sdialog d_mailchange
return 1
endif
endfor
endfor
src.tag0.e-mail=<argtxt[0]>
src.sysmessage @0487,,1 Ваш e-mail адрес: <src.tag0.e-mail>. Вы можете изменить его.
src.tag0.email=1
return 1
else
src.sysmessage @0487,,1 Неверный адрес электронной почты
sdialog d_mailchange
return 1
endif

ONBUTTON=2
return 1

[EOF]

Thank you!
12-03-2016 09:47 PM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Coruja
Sphere Developer
*****

Posts: 987
Likes Given: 5
Likes Received: 226 in 187 posts
Joined: Jul 2012
Reputation: 7

Dimension Shard

Post: #2
RE: MySQL & Symbols?
try using STRREGEX instead STRMATCH
http://wiki.sphere.torfo.org/index.php/C...0#STRREGEX

here you can quick check if your regex code is working properly: https://regex101.com/

Code:
IF (!STRREGEX("\W@.","<ARGTXT[0]>"))
  SYSMESSAGE The ARGTXT[0] have invalid symbols (it's only allowed A-Z words, 0-9 numbers, and @ . symbols)
ENDIF

also it's a good idea use an extra STRMATCH to check if the email format is correct
Code:
IF (!STRMATCH("*@*.*","<ARGTXT[0]>"))
  SYSMESSAGE The ARGTXT[0] is not a valid email format
ENDIF

PS: MySQL is compatible with symbols like \@!'$= etc, but to use them you must escape the data first (add backslash "\" before each symbol) to make the SQL command interpret these values as symbols

eg: to find an char named Test's on SQL table you must add \ before '
Code:
SELECT * FROM chars WHERE name="Test\'s" LIMIT 1

this is a security protection to prevent SQL injection exploits, so to make your SQL database secure, you must always escape the data and never trust user inputs on SQL commands

if you're executing SQL commands using EXECUTE instead AEXECUTE, you can use <DB.ESCAPEDATA <ARGS>> to escape data (this will automatically add \ before each symbol inside <ARGS>)
(This post was last modified: 12-04-2016 03:42 AM by Coruja.)
12-04-2016 03:34 AM
Find all posts by this user Like Post Quote this message in a reply
Llirik
Journeyman
*

Posts: 115
Likes Given: 0
Likes Received: 10 in 8 posts
Joined: Feb 2015
Reputation: 0

UO Forum

Post: #3
RE: MySQL & Symbols?
How be in this code?

on=@timer
db.query SELECT * FROM moneys WHERE UserEmail="<cont.tag0.e-mail>" and status="0" LIMIT 1 // when <cont.tag0.e-mail> have symbols error!
if <db.row.numrows>
cont.f_add_gold <db.row.MoneyAmount>
db.execute UPDATE moneys SET status="1" WHERE UserEmail="<cont.tag0.e-mail>" and TransactionID="<db.row.TransactionID>"
cont.sysmessage @color_o_gold <db.row.MoneyAmount> gp put in your bank!
endif

<DB.ESCAPEDATA <\>>???
(This post was last modified: 12-05-2016 06:38 AM by Llirik.)
12-05-2016 06:09 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Coruja
Sphere Developer
*****

Posts: 987
Likes Given: 5
Likes Received: 226 in 187 posts
Joined: Jul 2012
Reputation: 7

Dimension Shard

Post: #4
RE: MySQL & Symbols?
DB.ESCAPEDATA already add \ before all symbols automatically, you just need to replace <cont.tag0.e-mail> with <DB.ESCAPEDATA <cont.tag0.e-mail>>
12-05-2016 10:40 AM
Find all posts by this user Like Post Quote this message in a reply
Llirik
Journeyman
*

Posts: 115
Likes Given: 0
Likes Received: 10 in 8 posts
Joined: Feb 2015
Reputation: 0

UO Forum

Post: #5
RE: MySQL & Symbols?
(12-05-2016 10:40 AM)Coruja Wrote:  DB.ESCAPEDATA already add \ before all symbols automatically, you just need to replace <cont.tag0.e-mail> with <DB.ESCAPEDATA <cont.tag0.e-mail>>

Thank you very much! After test work well!
(This post was last modified: 12-06-2016 12:47 AM by Llirik.)
12-06-2016 12:46 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Llirik
Journeyman
*

Posts: 115
Likes Given: 0
Likes Received: 10 in 8 posts
Joined: Feb 2015
Reputation: 0

UO Forum

Post: #6
RE: MySQL & Symbols?
Last question: in the sphere have any command to escape <args>?

if (STRMATCH("*@*.*", "<escapedata <argtxt[0]>>")) //???

if I type the: "email@mail.ru")|" with this symbols console write:

19:55:ERROR:(email.scp,27)Undefined symbol ''
19:55:ERROR:(email.scp,31)Undefined symbol ''
19:55:ERROR:(email.scp,31)Undefined symbol ''
19:55:ERROR:(email.scp,31)Undefined symbol ''
19:55:ERROR:(email.scp,31)Undefined symbol ''
19:55:ERROR:(email.scp,31)Undefined symbol ''
19:55:ERROR:(email.scp,31)Undefined symbol ''

Maybe (STRMATCH("*@*.*", "<val <argtxt[0]>>"))?
Thank you!
(This post was last modified: 12-06-2016 03:11 AM by Llirik.)
12-06-2016 03:02 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Llirik
Journeyman
*

Posts: 115
Likes Given: 0
Likes Received: 10 in 8 posts
Joined: Feb 2015
Reputation: 0

UO Forum

Post: #7
RE: MySQL & Symbols?
I have bugs with "|" symbols and in rename function with <args> too.
12-06-2016 07:56 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Coruja
Sphere Developer
*****

Posts: 987
Likes Given: 5
Likes Received: 226 in 187 posts
Joined: Jul 2012
Reputation: 7

Dimension Shard

Post: #8
RE: MySQL & Symbols?
there's no way to escape strings on sphere scripting, the only thing that you can do in this case is use " " to force the string to be stored as text. But that's not valid for functions that already uses " " because and extra " "" will break the function too

so the best way to prevent parse errors is prevent symbols at the moment that you're storing the tag. No invalid tags = no parse errors

using your example STRMATCH("*@*.*", "<TAG.Email>")
where TAG.Email="email@mail.ru")|"

the script will become this: STRMATCH("*@*.*", "email@mail.ru")|")

note that the first STRMATCH value is "*@*.*", the second value is "email@mail.ru", and there's an extra )|" breaking the code. This would not happen if the email already got stored as email@mail.ru instead email@mail.ru")|
12-06-2016 09:39 AM
Find all posts by this user Like Post Quote this message in a reply
Llirik
Journeyman
*

Posts: 115
Likes Given: 0
Likes Received: 10 in 8 posts
Joined: Feb 2015
Reputation: 0

UO Forum

Post: #9
RE: MySQL & Symbols?
It is not error? But my Console wrong! Sad My dream a clear console! This is not bug?
(This post was last modified: 12-06-2016 10:06 AM by Llirik.)
12-06-2016 09:47 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Coruja
Sphere Developer
*****

Posts: 987
Likes Given: 5
Likes Received: 226 in 187 posts
Joined: Jul 2012
Reputation: 7

Dimension Shard

Post: #10
RE: MySQL & Symbols?
sure it was an console error, but it's not an sphere fault

sphere have the function, but the function expect that all args are valid strings. If it receive an invalid string that will break the code, it will break the code and return an console error telling you that it doesn't understand what this code must do

so the best fix to avoid functions reading invalid strings is: don't have invalid strings stored

eg: if you don't want the function reading an email like aaa@mail.com|!% that will break the code later, you must simply prevent the user to insert this email aaa@mail.com|!% at account registration screen. You can do it using the STRREGEX example above
(This post was last modified: 12-06-2016 10:40 AM by Coruja.)
12-06-2016 10:39 AM
Find all posts by this user Like Post Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)