Jump to content

MySQL UDFs (without ODBC)


Recommended Posts

Thanks for getting back to me.

One question that I have been maybe you could answer is the following. I am executing three SQL statements and have a multi statement flag turned on. The first statement executes successfully and the second one fails.

So what I find is that this third statement is not attempted and what is returned to the program is a zero and not a minus 1( that would indicate an error.)

Is this the correct behavior?

Steven

Link to post
Share on other sites
  • 4 weeks later...
  • Replies 189
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

MySQL UDFs using libmysql.dll functions: most functions from MySQL API all are prefixed with an underscore: _MySql... e.g.: _MySQL_Real_Query( sometimes parameters are chaged - read function descripti

I don't get it. @mLipok has polished (pun intended) a nice ADO UDF which works flawlessly. Why insist on using another one which causes issues?

@ProgAndy I like anything that smells to SQL !! Unfortunately you link does not work (for me ?). Rgds ptrex

Posted Images

Is this the correct behavior?

I am not familiar with mulitple statements, but it should be correct I think. The errors are propably returned later when cycling through the result sets.

Is it possible to run a stored function/procedure of MySQL by use this UDF?

It should be. You can do everything your user is allowed to do.

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to post
Share on other sites
  • 3 months later...

Nice one and thanks for the work.

I am having a big issue where sometimes compiled script will just hang. By checking log it seems it happens when the query fails and server doesnt return proper result.

I guess there is nothing to do about this since there is no error check and even if you compare mysql_success bool it wont help since that practically just tells you if it failed or not.

Link to post
Share on other sites
  • Moderators

Hate to state the obvious, but what is the query that, when fails, hangs?

Sounds like a MySQL server error not returning the proper response(s) to libMySQL, but just curious if I could replicate it on my end.

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Link to post
Share on other sites
  • 4 weeks later...

Very awesome UDF.Thanks a lot ProgAndy.

Here is few tips for users:

This is a user input:

$ds='uid`=7 or sleep(100)-- and ';

Your application is vulnerable if your input references to column name even if it's escaped.

(because mysql_real_escape_string() simply escapes ' and \ plus some unicodes but not `.)

$s=_mysql_real_escape_string($connected,$ds);

$query = "SELECT * FROM userstbl where `" & $s & "`='blah'"

Another scenario:

Again vulnerable.

$ds='sleep(10)--';

$s=_mysql_real_escape_string($connected,$ds);

$query = "SELECT * FROM userstbl where uid=" & $s

ConsoleWrite($query & @CRLF & @CRLF)

Second example isn't vulnerable if you properly typecast in ex user input to integer (if you expect integer from user input of course)

$ds=Int('sleep(10)--');

So always use:

$s=_mysql_real_escape_string($connected,$ds);

$query = "SELECT * FROM `userstbl` where `uid`='" & $s & "'"

Style and if possible do not use user input as reference to column names.(In itself it's bad style)

(if you do or you need to do that validate it using whitelisting ways: Check is user input exists in your array?)

On other hand here we deal with client MYSQL SERVER.(Missing server side validation/sanitisation in this case.)

It's not secure anymore if your client managed to modify your program or somehow managed Man in Middle attacks.

You can't do anything in this case.It will look like something like: "Validating authentication using Javacscript on login page".

If i'm wrong please correct me.

Thanks.

[size="5"] [/size]
Link to post
Share on other sites

Your application is vulnerable if your input references to column name even if it's escaped.

Would this not be solved if the authentification only allows usage, select on the database ? This could, as I see it, be handled server-side, so that any attempt to create DROP, DELETE, UPDATE, INSERT etc queries from a select only would resolve in an "access denied" error.

REVOKE ALL PRIVILEGES ON db.* FROM username;
GRANT SELECT ON db.* TO username;

Not that I don't agree with you. But it would make the problem less of an issue ?

Of course SQL injection on SELECT will allow selection of more data / columns than script normally is built for. But not destructive behavior, or ?

I am just a hobby programmer, and nothing great to publish right now.

Link to post
Share on other sites

In itself select privilegie is enough to overhelm completely remote MYSQL server if your application vulnerable.

Think about what will happen if client managed to submit 20~30 benchmark() to mysql server using your vulnerable client programm.

Even if you revoke all *privileges* from mysql user except select privilegie "union"-ing will do it's own job.(So also confidentiality will be broken)

The best practice for programmers is that sanitise and validate user input.

As i pointed it out before in our situation AutoIT works something like this:

May be Malicious user ____________ INPUT ____________ Autoit Application ____________ STAGE 3  ____________ MYSQL SERVER

Notice flaw in this scenario:

Theris no any mechanism to *additionally* sanitise user input on STAGE 3 (Missing server side validation/sanitization)

IMO more secure than first example:

May be Malicious user ____________ INPUT ____________ Autoit Application ____________ VERIFY AND VALIDATE IN EX USING PHP(Webserver) ____________ MYSQL SERVER

But requires a lot of work.

Edited by Fire
[size="5"] [/size]
Link to post
Share on other sites

...

IMO more secure than first example:

May be Malicious user ____________ INPUT ____________ Autoit Application ____________ VERIFY AND VALIDATE IN EX USING PHP(Webserver) ____________ MYSQL SERVER

But requires a lot of work.

Thanks for enlightening me !

Is what you mean to have AutoIT sumbit by URL to a PHP script, which could in essense look like

// http://bobby-tables.com/php.html
$stmt = $db->prepare('update people set name = ? where id = ?');
$stmt->bind_param('si',$name,$id);
$stmt->execute();

.. and this would spew out result or error from MySQL server..

I am just a hobby programmer, and nothing great to publish right now.

Link to post
Share on other sites

Do you mean something like this?

SELECT COUNT(*) FROM db WHERE field=value

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to post
Share on other sites

Execute the command and fetch the result, I think like this:

_MySQL_Real_Query

_MySQL_Store_Result

$result = _MySQL_Fetch_Row_StringArray

$count = $result[0]

_MySQL_Free_Result

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Link to post
Share on other sites
  • 3 months later...

Please help!

I'm using this udf for a project and it work very fine. i want to use my data after query, not only to post there in an array. example in php:

$sql = mysql_query("select title where id='prog@ndy'");

$res = mysql_fetch_row($sql); $value = $res[0]; //And now i can use $value wich contain request value

i want to do that kind of things in the udf, but the _MySQL_Fetch_Row() return empty when i try

thankx for the good job Prog@ndy!

Link to post
Share on other sites
  • 1 month later...

Good afternoon.

There was a problem when calling a stored procedure mysq

protsedural itself (checks whether there is such a column, if not, create it):

DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `AddColumnUnlessExists`(
IN dbName tinytext,
IN tableName tinytext,
IN fieldName tinytext,
IN fieldDef text)
begin
SET NAMES cp1251;
IF NOT EXISTS (
SELECT * FROM information_schema.COLUMNS
WHERE column_name=fieldName
and table_name=tableName
and table_schema=dbName)
THEN     set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,' ADD COLUMN ','`',fieldName,'`',' ',fieldDef);
prepare stmt from @ddl;
execute stmt;
END IF;
end

If you call it on My SQL Workbench, then everything works.

If you call her in autoit, the procedure does not seem to call, nothing happens

#include "mysql.au3"
Local $UN = "root"
Local $PW = "566434"
Local $DB = "esi"
Local $SVR = "localhost"
Local $Port = "3306"
Local $Vesi="12.3"
    
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "")
;MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info())
$MysqlConn = _MySQL_Init()  
$connected = _MySQL_Real_Connect($MysqlConn,$SVR,$UN,$PW ,$DB,$Port)
_MySQL_Set_Character_Set($MysqlConn,"cp1251")
If $connected = 0 Then
$errno = _MySQL_errno($MysqlConn)
MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
Endif
    
$option="$MYSQL_OPTION_MULTI_STATEMENTS_ON"
_MySQL_Set_Server_Option($MysqlConn, $option)
$k="123"
$zapros3 = 'call esi.AddColumnUnlessExists(''esi'', ''cars'',''testtest'', ''varchar(32) null'');'
_MySQL_Query($MysqlConn,$zapros3)
where esi- database name, cars- table name

In what could be the problem? I'm not calling the procedure correctly?

Link to post
Share on other sites
  • 6 months later...
  • 8 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By DevMode
      welcome
      I need help dealing with a C++ DLL
      Specify that I need help with how to use this function
      struct libusb_device; typedef struct libusb_context libusb_context; ssize_t LIBUSB_CALL libusb_get_device_list(libusb_context *ctx,libusb_device ***list); My problem is how to deal with "libusb_device ***list"
      my code 
      $libusb_device = DllStructCreate("PTR") Func libusb_init() $Var = DllCall($libusb0, "ptr", "libusb_init",'ptr',$_libusb_device_handle_Ptr) If @error Then Return False Return $Var[1] EndFunc Func libusb_get_device_list() MsgBox(0,VarGetType($_libusb_device_handle_Ptr),$_libusb_device_handle_Ptr) $Var = DllCall($libusb0, "int", "libusb_get_device_list","ptr",$_libusb_device_handle_Ptr,"ptr*",POINTER(POINTER($libusb_device))) $Error = @error If $Error Then Return False MsgBox(0,VarGetType($Var),$Error&@CRLF&$Var) _ArrayDisplay($Var) Return $Var[1] EndFunc  
      Thank you
      iLibUSB.dll iLibUSB_32.dll libusb.h
    • By bobflumox
      Hi all,
      My programming knowledge is very basic.
      I have an old script that creates shares and assign permissions. It normally registers SetAcl.ocx if necessary and creates an object to assign permissions.
      The command that registers SetAcl was apparently working fine under Windows 7 but is not working under Windows 10.
      RunWait("regsvr32.exe path\to\setacl.ocx /s", "", @SW_HIDE) As I'm logged in as admin, I changed this command to :
      RunAsWait(@UserName, "", "", 0, "regsvr32.exe path\to\setacl.ocx /s", "", @SW_HIDE) It seems to terminate correctly but the script still doesn't work as expected.
      To check that, I've created that small script :
      Local $objSetAcl = ObjCreate("SETACL.SetACLCtrl.1") If IsObj($objSetAcl) Then ConsoleWrite("Object successfully created." & @CRLF) Else ConsoleWrite("Object not created. Registering SetAcl.ocx" & @CRLF) Local $result = RunAsWait(@UserName, "", "", 0, "regsvr32.exe path\to\setacl.ocx /s", "", @SW_HIDE); Use of my admin username to elevate CMD ConsoleWrite("Return code : " & $result & @CRLF) ConsoleWrite("Creating object" & @CRLF) $objSetAcl = ObjCreate("SETACL.SetACLCtrl.1") If IsObj($objSetAcl) Then ConsoleWrite("Object successfully created." & @CRLF) Else ConsoleWrite("Object creation failed." & @CRLF) EndIf EndIf It tries to register SetAcl.ocx, return code 0 seems to be fine but still can't use SetAcl.
      But if I go to CMD as admin, run the regsvr32 command and restart my script, it can create the object without issue.
      I know my poor knowledge makes me miss something. Anyone can help me figure this out ?
    • By Gowrisankar
      Dear members of the forum,
      I'm working on a project in which I have to use Image recognition technique. 
      Due to client restrictions, I couldn't use AutoIt for this project. 
      Is there a way to use this DLL "ImageSearchDLL.dll" (which is used to do image recognition steps in AutoIt) in VB.Net to achieve the same result? 
      I have used this DLL few years before and got good results. If there is a latest version of this DLL and if you can share it, that will be helpful too.
      Any guidance is deeply appreciated.
    • By Colduction
      Hi AutoIt Scripters/Programmers. I have a question about MIME Tools for Notepad++:
      I've recently found a UDF about Base64 in forum, but they can't decode\encode correctly some emojis , other UTFs and etc. so i decided to use mimeTools.dll of Notepad++ or main site
      My problem is how to use this dll in AutoIt Language?

      I will be happy with your comments and answers❤ Thanks.
    • By FrancescoDiMuro
      Good morning Forums 
      In these days, I am working on a project that involved me to use some Windows APIs to obtain some information about Terminal Servers.
      I'm doing this using wtsapi32.dll in a VBA Project, but, the lack of knowledge about few things threated in the articles make this quite difficult to
      implement and understand at the same time.
      The most difficult thing I'm facing is "translating" C/C++ functions or struct in VBA when pointers are used, or pointers of pointers, and so on.
      Since VBA seems to not have a pointer type, to make those functions work I need to implement other functions taken from other DLLs, and this confuses me a lot.
      For example, starting from this code, I splitted all the functions and all the definitions to understand why they are there, and why I need to use them.
      At the end, I've found out that the code I was going to implement starting from the functions provided in the Microsoft Docs won't be ever be able to work without some supplementary functions which are not mentioned anywhere.
      So, I was wondering if someone would please point me out to a good and practical exaplanation about pointers (in general) or specifically for VBA, because I need to use them quite often in these days, and I'd like to understand what I am doing.
      Thanks in advance.

      Best Regards and Stay at home 🏡
       
×
×
  • Create New...