Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: convert oracle procedure to mysql

    I am trying to convert an Oracle stored procedure to work with MySQL but I am getting an error on line 9. How can I get this working in MySQL?

    DELIMITER $$
    DROP PROCEDURE IF EXISTS VulnerabilitiesScoring$$
    CREATE PROCEDURE VulnerabilitiesScoring()
    BEGIN
    DECLARE cvssStartval INT;
    DECLARE cvssScore varchar(30);
    DECLARE cceStartVal INT;
    DECLARE cceValue varchar(30);
    DECLARE rowCounter INT;
    cursor c1 is select i from vulnerabilities_internal_test;
    rowCounter:=1;
    for j in c1 loop
    cvssStartVal:= instr(j.i, 'CVSS');
    cvssScore:=substr(j.i, cvssStartVal+18, 3);
    cceStartVal:= instr(j.i, 'CCE');
    cceValue:=substr(j.i, cceStartVal+4, 6);
    dbms_output.put_line('Data for row ' || rowCounter);
    if cvssStartVal !=0 then
    dbms_output.put_line('CVSS Base Score: ' || cvssScore);
    else
    dbms_output.put_line('CVSS Base Score: no score for this row');
    end if;
    if cceStartVal!=0 then
    dbms_output.put_line('CCE: ' || cceValue);
    else
    dbms_output.put_line('CCE: no score for this row');
    end if;
    dbms_output.put_line(' ');
    rowCounter:=rowCounter+1;
    end loop;
    end$$
    DELIMITER ;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You need to include a DECLARE for your cursor definition. I have an example stored procedure which uses cursors here
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2012
    Posts
    8
    I looked through this and couldn't make much sense of it. I made some changes but am still getting errors:

    DELIMITER $$
    DROP PROCEDURE IF EXISTS VulnerabilitiesScoring$$
    CREATE PROCEDURE VulnerabilitiesScoring()
    BEGIN
    DECLARE cvssStartval INT;
    DECLARE cvssScore varchar(30);
    DECLARE cceStartVal INT;
    DECLARE cceValue varchar(30);
    DECLARE rowCounter INT;
    DECLARE c1 CURSOR FOR
    SELECT i from vulnerabilities_internal_test;
    for j in c1 loop
    read_loop: LOOP
    cvssStartVal:= instr(j.i, 'CVSS');
    cvssScore:=substr(j.i, cvssStartVal+18, 3);
    cceStartVal:= instr(j.i, 'CCE');
    cceValue:=substr(j.i, cceStartVal+4, 6);
    dbms_output.put_line('Data for row ' || rowCounter);
    if cvssStartVal !=0 then
    dbms_output.put_line('CVSS Base Score: ' || cvssScore);
    else
    dbms_output.put_line('CVSS Base Score: no score for this row');
    end if;
    if cceStartVal!=0 then
    dbms_output.put_line('CCE: ' || cceValue);
    else
    dbms_output.put_line('CCE: no score for this row');
    end if;
    dbms_output.put_line(' ');
    rowCounter:=rowCounter+1;
    end loop;
    end$$
    DELIMITER ;

  4. #4
    Join Date
    Mar 2012
    Posts
    8
    I may be going at this wrong, but this is what I am trying to do:

    I have a lot of unmanageable data from a network scanning tool and I am trying to split out certain data into separate columns and match them up to the row they came from. I added some sample data above. There is a lot of junk in there and since its coming straight out of a proprietary tool I don't have much control over cleaning it up.

    Sample rows:

    \nSynopsis :\\\Windown \n\n"Medium" / CVSS Base Score : 3.3\n(\r\n\r\n -------------\n\nCVE : CVE-2001-9999, CVE-1999-1211\nBID\n"Other references" : OSVksjdnflksdf,sd flsdfpsdfmp\n

    \nSynopsis :\n\n \n\n"Medium" / CVSS Base Score : 4.3\n(\r\ghavbdfoasdmnf amsdf\n\r\n--------n\nGPO:Configuration\\Window-----\n\nCVE : CVE-1001-55 55, CVE-1001-8888\nBID

    \CCE-2699-7:test.exePermissions\": [ERROR]\n\n\nGPO: Computer Configuration\ \Windows Settings\\Se"

    \CCE-1909-1:twon.exePermissions\": [ERROR]\n\n\nGPO: Computer Configuration\ \Windows Settings\\Se"

    \CCE-2731-8:test3p.exePermissions\": [ERROR]\n\n\nGPO: Computer Configuration \\Windows Settings\\Se"


    From this data I'd like to have a procedure I can run that will create separate columns for the cvss Base Score and CVE and CCE values from this data and match them up with the rows they were pulled from.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Did you have a look at the example that I suggested above. From your code I see that you have never opened the cursor. You also need to fetch the values into variables so that you can then use these values.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Mar 2012
    Posts
    8
    Yes, I did. I've changed it to this:

    DELIMITER $$
    DROP PROCEDURE IF EXISTS VulnerabilitiesScoring$$
    CREATE PROCEDURE VulnerabilitiesScoring()
    BEGIN
    DECLARE cvssStartval INT;
    DECLARE cvssScore varchar(30);
    DECLARE cceStartVal INT;
    DECLARE cceValue varchar(30);
    DECLARE row_count INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR
    SELECT i from vulnerabilities_internal_test;
    OPEN cur1;
    read_loop: LOOP
    cvssStartval:= instr(j.i, 'CVSS');
    cvssScore:=substr(j.i, cvssStartVal+18, 3);
    cceStartVal:= instr(j.i, 'CCE');
    cceValue:=substr(j.i, cceStartVal+4, 6);
    DBUG_PRINT('Data for row ' || row_count);
    if cvssStartVal !=0 then
    DBUG_PRINT('CVSS Base Score: ' || cvssScore);
    else
    DBUG_PRINT('CVSS Base Score: no score for this row');
    end if;
    if cceStartVal!=0 then
    DBUG_PRINT('CCE: ' || cceValue);
    else
    DBUG_PRINT('CCE: no score for this row');
    end if;
    DBUG_PRINT(' ');
    row_count:=row_count+1;
    end loop;
    CLOSE cur1;
    end$$
    DELIMITER ;


    I am still getting errors. the := in the line cvssStartval:= instr(j.i, 'CVSS');
    is underlined

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Again if you look at the link to the example that I have given previously you will see how values are assigned in MySQL stored procedures. If you just spend a little bit of time going through this most of your issues will be resolved very easily. Try this for assignments within stored procedures.

    SET lstart = 1;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Mar 2012
    Posts
    8
    I could not get this working but perhaps you could help me with this:

    Code:
    SELECT textTag, LOCATE('CVE-',textTag, 16) as CVEs FROM test1  WHERE locate('CVE-',textTag)
    I am attempting to return all of the occurrences of a string within a row and not return any other data. Some rows only have one occurrence such as CVE-1000 . While other rows may have the following: CVE-1500, CVE-1160, CVE-1400 etc.

    Is there a way to return all the occurrences (not just the first, which is what I am getting now) ?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •