MySQL UDFs (without ODBC)

[shx]

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

[vladu]

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

Thank you in advance for your help,

Vlad

[ProgAndy]

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.

[Aktonius]

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.

[SmOke_N]

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.

[Fire]

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.

[Myicq]

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 ?

[Fire]

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 August 29, 2012 by Fire

[Myicq]

...

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

[Hoox]

Anyone know how I could make a Count with this?

I need a variable that contains the number of records that contains a specific field.

[ProgAndy]

Do you mean something like this?

SELECT COUNT(*) FROM db WHERE field=value

[Hoox]

Do you mean something like this?

SELECT COUNT(*) FROM db WHERE field=value

Yes, this is it. How do I get this in a variable?

[ProgAndy]

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

[nmousdevlop]

VERY GOOD JOB Prog@ndy!! that's what i need!!!!

Thank you for this job. I want to do somethings for the developpement of this langage.

You ar the BEST

[nmousdevlop]

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!

[nmousdevlop]

yep!

i didn't view the last post of Prog@ndy about ftch result!

Let me try this !!

[nmousdevlop]

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

Yesss! it work !!! Thankx!

[Rivega]

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?

[Neutro]

Thank you very much for this UDF ProgAndy!

It helped me a lot for what I wanted to do !

[possy_99]

is this still being maintained? dead link again it seems..